[SOLVED] Add calculated item to pivot table

tony0710 · #1 06-21-2009, 05:41 PM

Dear All

Attached is a worksheet example, of my beauty salon with 5 therapists.

The input sheet - is the shhet into which I input data on a daily basis.

Weekly inc - This sheet shows the total that each therapist made in apivot table based on the input. The week numbers are 1 to 52 (or 53) depending on each financial year (07/08, 08/09 etc).

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? This is to be done automatically by excel.

This commission is calcluated by using the target comm figures - E.G. Leanne's target is £480 - so she has to earn £480 a week before getting a commission. So if in a week she earns £500 - she will get a commission on £20 (£500 - £480). The commission is 10%, so she will earn £2 that week.

Any help would be much appreciated.

**rylo** · #2 06-22-2009, 01:29 AM

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

tony0710 · #3 06-22-2009, 11:57 AM

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

**rylo** · #4 06-22-2009, 06:26 PM

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

tony0710 · #5 06-24-2009, 04:54 PM

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?

**rylo** · #6 06-24-2009, 06:08 PM

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

Quote:

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

tony0710 · #7 06-25-2009, 06:01 AM

Dear Rylo

You have understood my brief correctly.

I've just re-checked all my formulas and it appears that it was my error - your formulas work - but they have now thrown up another problem - I'm getting Negative com values.

See attached revised sheet - all negative values have been coloured red, and I've taken one of the values and highlighted it yellow and tried to work out why this happened.

tony0710 · #8 06-25-2009, 09:44 AM

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

**rylo** · #9 06-25-2009, 06:13 PM

Hi

Have you tried to zip the file before attaching?

rylo

tony0710 · #10 06-26-2009, 09:07 AM

Thanks Rylo

Perhaps the ordinary xls file was too big - but please find attached file and hope that you can see the problem at hand.

Thanks for any further help.

tony0710 · #11 06-27-2009, 04:46 AM

any way of getting rid of the negative values?

**rylo** · #12 06-28-2009, 06:29 PM

Hi

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

Try

Quote:

=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

tony0710 · #13 07-04-2009, 10:56 PM

Wow Thanks Rylo,

That works a treat.

That has resloved the original brief that I presented - but I've just been informed by my business partner that we may introduce a second target - so would like to adapt the formulaes that you have provided:

Aimee and Bev - would get an extra £10 on reaching a weekly target of £850
Leanne and Gemma S would get an extra £10 on reaching a weekly target of £600

So the 10% will still remain, but an extra £10 will be given on reaching the second target.

I think I have resloved it by following your solution above:

Added 2nd target column to tab "Com Target"

In input - added column N - to show second target according to look up:

Code:
```
=VLOOKUP(B2,'Com Target'!$A$2:$C$7,3,FALSE)
```

**rylo** · #14 07-05-2009, 11:38 PM

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:

Quote:

=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

tony0710 · #15 07-06-2009, 06:23 PM

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

Welcome to the Excel Forum

Forum Statistics