Add calculated item to pivot table

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

I'm having trouble in attaching new file - saya that there is a database error - please help.

Hi

Have you tried to zip the file before attaching?


rylo