DAX table search to apply discount based on ranges

Question

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

Answer 1

Hi @Shinka

One way assuming the below Tables

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

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

Corresponding sample availL here

Answer 2

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

Answer 3

@Shinka

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%")

---

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.