question

Shinka-3536 avatar image
0 Votes"
Shinka-3536 asked Lz-3068 commented

DAX table search to apply discount based on ranges

Hi everyone,

I wish to create a DAX measure that uses sales information on one table and brings the discount % from another table. The catch is that the tables are not related and the discount varies depending on how much the client has bought.

Please find an excel example here:

https://1drv.ms/x/s!AmrjlXSYqMxegasoBA3or43uSU6DGw?e=0hCm8y

Thanks in advance for any help

office-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
1 Vote"
Lz-3068 answered Lz-3068 commented

Hi @Shinka-3536

One way assuming the below Tables

120556-demo.png

Measure Total Sales:
=SUM(TableSales[Amount])

Measure Discount:
=MAXX (
FILTER ( TableDiscount, TableDiscount[Threshold] <= TableSales[Total Sales] ),
TableDiscount[Discount %]
)

Corresponding sample availL here



demo.png (74.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Worked great! Thank you!

0 Votes 0 ·

Glad I could help @Shinka-3536 & Thanks for posting back

0 Votes 0 ·
emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered Shinka-3536 commented

@Shinka-3536

My measure formula (measure 1) is as following, Lz-3068's formula is more simpler.
SWITCH(TRUE(),[Sum of Amount]>=0&&[Sum of Amount]<1000,"0%",[Sum of Amount]<10000,"1%",[Sum of Amount]<50000,"5%",[Sum of Amount]<100000,"10%",[Sum of Amount]>=100000,"25%")

120637-image.png


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


image.png (58.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Emily. I favoured Lz's approach so as to not hardcode the ranges.

1 Vote 1 ·

@Shinka-3536
Is there any update or question?
If yes, you may post back.

0 Votes 0 ·

Sorry, had a personal issue and had to take a few days off. Answering now.

0 Votes 0 ·
HerbertSeidenberg-6295 avatar image
0 Votes"
HerbertSeidenberg-6295 answered

Excel 365 Pro Plus with Power Pivot and Power Query.
Mostly done with "M" in PQ, not "DAX" in PP.
https://www.mediafire.com/file/7fclmfdpgml8u9l/08_04_21.xlsx/file
https://www.mediafire.com/file/qaifpwvq5kyt2az/08_04_21.pdf/file

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.