cancel
Showing results for
Did you mean:
Regular Visitor

## PQ Apply sales discount depending on value of order

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

## Re: PQ Apply sales discount depending on value of order

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!

3 REPLIES 3
Super Contributor

## Re: PQ Apply sales discount depending on value of order

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.

Proud to be a Datanaut!

Regular Visitor

## Re: PQ Apply sales discount depending on value of order

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
Highlighted
Regular Visitor

## Re: PQ Apply sales discount depending on value of order

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!

Announcements

Power BI Super User, Greg Deckler, explains

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 40 members 974 guests
Recent signins: