Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dan80
Helper II
Helper II

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

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!

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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.

 

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.