CALCULATING THE VOLUME DISCOUNT amount ( giving a certain period , customer AND product)
What’s the problem?
Production is all year but starts increasing rapidly in october year N till march N+1.
This cause a problem in inventory ( where to store the products? ) .
So in order to solve this storage problem customers are encouraged to buy the products in this giving period and store them in their own storage facility.
This comes of course at the price:
A discount rate is offered at the customers but not all customers receive the same discount rate
• Top customers A B C receive other discount % then customer D to Z
• These % are valid for the sales from 01/09/N till 31/ 03/N+1
• Discount % are valid only for product A and B and C
customer A customer C cumul turnover cumul turnover
from to % from to %
0 5.000.000 0,00% 0 3.000.000 0,00%
5.000.000 10.000.000 0.10% 3.000.000 5.000.000 0.10%
10.000.000 15.000.000 0,20% 5.000.000 10.000.000 0.20%
15.000.000 20.000.000 0,30% 10.000.000 15.000.000 0.30%
20.000.000 99.000.000 0,40% 15.000.000 99.000.000 0.40%
customer B customer D tot Z (rest) cumul turnover cumul turnover [discount rate calc.pbix|attachment](upload://fp13PVs2UnfXWLBoVfNeJKo7sYi.pbix) (1.6 MB) ![result discount calc|690x383](upload://guOhZdu9030HKJxorBOzIKyVOea.jpeg)
from to % from to %
0 2.000.000 0,00% 0 2.000.000 0,10%
2.000.000 4.000.000 0.08% 2.000.000 4.000.000 0.15%
4.000.000 8.000.000 0.10% 4.000.000 8.000.000 0.20%
8.000.000 10.000.000 0.12% 8.000.000 15.000.000 0.25%
10.000.000 99.000.000 0.20%
Having a fact table with all the needed data for two years ( date, customer, sales, product #,…)
What is the best practice to tackle this problem with power BI?
Need a weekly/monthly report to take into account the provision.
I made a power BI file that does the calculation for customer A.
As I just started with power BI this can be improved in many ways?
How can this be made more dynamic when % change and products change
the pic shows how the result should look
for customer 1 if cum sales in the given period is between 2 m and 4 m then sales x %
i succeeded in making a measure but it looks awfull with a lot of constants/text
and want to optimise this measure
any help is greatly apreciated