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

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

any way of getting rid of the negative values?

Hi

Hadn't realised that you would have multiple years in the same sheet. My bad.

Try

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

rylo

Hi

Second one first. I'd just have the different formula from the initiaing time frame.

Now for the hard part.

The column O formula should be:

=IF(AND(K2>N2,OR(B2="leanne",B2="Gemma S",B2="Bev",B2="Aimee")),M2+10,M2)

Otherwise it will never have a match - B2 can't be leanne AND Gemma AND.....

However, even with that fix, you will get problems. The way it stands, if you have multiple entries that are > than the threshold, then they will ALL get the additional $10. They way I read it they should only get the extra $10 once - or have I read that incorrectly???

IF they should only get it once per week, not for every item over the threshold, then I'll have to think (that always hurts!) of some way to determine either the first or last appearance of the items and only apply to that instance. Can you clarify how many times they should be getting the extra?


rylo

Hi Rylo

You are right!!

The extra £10 is only added ONCE in that week, when that target is reached.

Am I asking for the impossible

Hi

Try the formulas below, and do a really good test of the result. I think it is OK. I've done it from the start of the data to make sure it has enough data to do a full test.

You will have to expand the range, but that can be worked on later.

N2: =VLOOKUP(B2,'Com Target'!$A$2:$C$7,3,FALSE)
O2: =IF(AND(K2>N2,OR(B2="leanne",B2="Gemma S",B2="Bev",B2="Aimee"),SUMPRODUCT(--($B$2:B2=B2),--($E$2:E2=E2),--($G$2:G2=G2))=SUMPRODUCT(--($B$2:$B$500=B2),--($E$2:$E$500=E2),--($G$2:$G$500=G2))),M2+10,M2)

Copy down.

This should find the last instance of the successful commission payment for the person, and add the bonus.


rylo

Thanks Rylo

That worked a treat - put it through it's paces last night - no problems - so all solved now.

Thanks for your time and patience.

Tony