cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dan80 Regular Visitor
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 RangeSales Upper RangeDiscount
01005%
10050010%
500100015%
1000200020%
2000500025%
500010000030%

 

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 SalesDiscount
54915%
109920%
30110%

 

Any ideas on how to achieve this in power query??  Thanks,

 

dan

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dan80 Regular Visitor
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
Ross73312 Super Contributor
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.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Dan80 Regular Visitor
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
Dan80 Regular Visitor
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!

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 974 guests
Please welcome our newest community members: