Add calculated item to pivot table

Hi

As you have some formula in the input sheet, how about adding some additional formulas that will work out the commission, and you could then add those to the pivot table.

Using your example file sheet Input
K1: *** Sales
L1: Commission Target
M1: Commission
K2: =SUMPRODUCT(--($B$2:B2=B2),--($G$2:G2=G2),($D$2:D2))
L2: =VLOOKUP(B2,'Com Target'!$A$2:$B$7,2,FALSE)
M2: =IF(K2>L2,(K2-L2)*0.1,0)

Copy from K2:M2 down and this will give you the relevant commission per sale.

HTH

rylo

Thanks

Had to tweak formula as follows:

K2:=SUMPRODUCT(--($B$2:B2=B2),--($G$2:G2=G2),--($H$2:H2=H2),($D$2:D2))

This took into account the particular quarter.

Still got a problem tho:

Using the above formula gives me a running total - but as soon as the total goes over the target com - Cell M is populated according to formula:

M2: =IF(K2>L2,(K2-L2)*0.1,0)

Then in the pivot table I get a total of the values above the target com - see attached revised worksheet.

What I need is a way of recording the Max value found using the sumproduct formula above in the pivot table and to ignore the other values for that member of staff on a particular week.

Regards

Hi

Sorry, I missed a bit.

M3: =IF(K3>L3,(K3-L3)*0.1-SUMPRODUCT(--($B$2:B2=B3),--($G$2:G2=G3),($M$2:M2)),0)

Copy from M3 down as required.

rylo

Thanks Rylo

But this has still not worked!

This still does not erdicate the value in col M when value in col K is NOT the highest for that therapist in that week.

Using the above formula you suggested and placing it in M3, then selecting it and using the corner handle to copy it down col M - I still get Row 17 showing that Aimee made £714.10 on 08/06/07 (week 2 of yr 07/08 Q1) and there is a value in M17 as 714.10 is above 600 - but this should be "zero", as this is not the MAX value that Aimee has earned in week 2 of Yr 07/09 Q1 - in fact her Max value was earned on Row 20 (09/06/07) - that also has a value in M20.

The pivot table adds the values of M17 and M20 to give 24.98 - when infact her total commission for the week is value in M20 only i.e. 13.57.

still needs more teaking - the formula:

M17: =IF(K17>L17,(K17-L17)*0.1-SUMPRODUCT(--($B$2:B16=B17),--($G$2:G16=G17),($M$2:M16)),0)
M20: =IF(K20>L20,(K20-L20)*0.1-SUMPRODUCT(--($B$2:B19=B20),--($G$2:G19=G20),($M$2:M19)),0)

Are these correct?

Hi

Aimee's total sales for week 2 is 849.80 as shown in K20. So here commission for the week should be 24.98. If you add the values from M17 and M20, you get 24.98.

What this formula is doing is showing in column M the commission component for each of the sales when here total sales is over the threshold. It doesn't try to find the max sales value and only apply the commission to that entry.

As you are using a pivot table, which will add all the commissions, I didn't thing that this would be an issue.

Your original brief wanted

What I want to do is either have another pivot table or to add an calculated item to the weekly inc pivot table showing me how much commission each therapist took each week?

I've taken the pivot table option which will add all the commission components and give the total commission for the therapist for the week.

Can you check that the total commision when totaled for each week is correct?

rylo