Latest Enterprise DNA Initiatives

Complex discount calculation ( time period,customer,products)

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
Restrictions:

• 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

roger

Hi @Roger, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Hi @Roger, we’ve noticed that no response has been received from you since the 10th of January. We are waiting for the masked demo pbix file, images of the entire scenario you are dealing with, and any other supporting links and details.

This will help other users and experts to answer your inquiry better. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

The pbix file and a word doc were joined with the initial question

Hi @Roger. Please upload your supporting documents again for the forum members to pursue (… I don’t see them in your initial post from 2 weeks ago, just in case anyone else is having the same issue).
Greg

attached the files for the calculation

discount rate calc.pbix (1.6 MB) discount problem in PowerBI .docx (18.2 KB)

why is this post withdrawn? posted the PBIX file and the word doc .
I think this is a very interesting topic worth a tutorial !

roger

i deleted my message because i was going to stated that your files wasn’t in the posting but then saw that it was posted

sorry