Hi
I am seeking some expert advice on how best to create a measure for a pivot table to calculate a rebate amount based on invoice values over a 3 month period. I have attached a workbook with a simplified worksheet with the dataset being used, pivot and manual table showing the results.
The period is not related to calendar quarters but a contract year (started from the commencement date). The rebate is based on the total value of charges in a 3 month period and is based on the incremental value in that period. e.g.
Total Charges - £3,753,513.95
1% of £3,000,000 , 1.25% of £500,000, 1.5% of £253,513.95
I am open to any suggestions on how to best achieve my desired outcome.
I am not sure there is a particulary easy way, but might be able to make it more dynamic.
Just a question at the moment - in Period 1 you are calcing 1% of 2.7m, and in period 2 you are calcing 1% of 3m and 1.25% of the balance (and so on). But the table states that under 2m there is no discount.
So i would have thought that period 1 should be
2,000,000*0%=0
776,676.76*1%=7766.76
and Period 2
2,000,000*0%=0
1,000,000*1%=10000.00
71621.16*1.25%=895.26
Or have i just read the table wrong?
Hi
Thanks for the reply and apologies for any confusion with the discount table. The discount is triggered if the total value in a period exceeds £2M and then the percentage values applied as per my explanation, so in the case of P1 the 1% is applied to the whole £2.7M. The first row of the discount table can be ignored as I agree it is misleading. Hope this makes sense.
To clarify my example above.
Value for period is £3,753,513.95 - this triggers volume rebate as it exceeds £2M
1% applied to the first £3M = £30,000 1.25% applied to the next £500,000 = £6,250 1.5 % to the next £500,000 (actual £253,513.95) = £3,802.71 2% would be applied to any amount above £4M
My goal is, if possible, to automate this calculation and update as each monthly dataset is imported and update a simple dashboard I am going to create.
That makes sense, thanks.
To avoid complication and the fact that it is in a dashboard i would go with a Staging table to calc everything and the dashboard to show it.
You can then have the value calculating and the rebate in another column summed to the total.
There are a few benefits doing it this way as you can seeing calculations are happening, you can adjust the rates/thresholds easily and the spreadsheet is likely to be quicker and more reliable than with a funky super calculation.
Not want you really wanted but a possible alternative?
Purfleet
Hi
Many thanks for your help, this seems the best approach to take on this stage of the calculation. The reason I thought about a Pivot calculation was to allow me to calculate the % that would be due to each business function based on their charges during each period. I think I can use a similar approach to this using the staging table as the base information.
