PQ Apply sales discount depending on value of order

[Dan80]

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

[Anonymous]

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.

[Dan80]

Thanks Ross for your reply, appreciate it. However, I need to do this calculation within power query as I have a range of other scenarios to fulfil as well. Thanks though

[Dan80 · Accepted 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!