Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am working with a pricing table with 100's of different scenarios, but simplified, the table is:
Sales Lower Range | Sales Upper Range | Discount |
0 | 100 | 5% |
100 | 500 | 10% |
500 | 1000 | 15% |
1000 | 2000 | 20% |
2000 | 5000 | 25% |
5000 | 100000 | 30% |
I need to merge the discount with the sales data but don't know how to 'get a match' on the total sale value (in the other table) and the above table when it is in a range. Below is an example of what I need.
Total Sales | Discount |
549 | 15% |
1099 | 20% |
301 | 10% |
Any ideas on how to achieve this in power query?? Thanks,
dan
Solved! Go to Solution.
Ross - you have given me an idea!!!
I can merge the pricing table into the sales data, bringing across the upper and lower ranges and then use a similar formula to what you have suggested, but in power query, to find the appropriate discount rate. Testing the logic now. Thanks for your prompt!
In your Total Sales table for the discount column, we will make this column a DAX column. Try using this formula:
Discount = CALCULATE( SUM('SALES TABLE'[Discount]), 'SALES TABLE'[Sales Lower Range] <= 'Sales Total Table'[Total Sales], 'SALES TABLE'[Sales Upper Range] >= 'Sales Total Table'[Total Sales] )
I've assumed the name of your tables here, but this should give you an idea of what i'm thinking.
Ross - you have given me an idea!!!
I can merge the pricing table into the sales data, bringing across the upper and lower ranges and then use a similar formula to what you have suggested, but in power query, to find the appropriate discount rate. Testing the logic now. Thanks for your prompt!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |