# DAX table search to apply discount based on ranges

131 Reputation points
2021-08-04T17:07:10.037+00:00

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:

Thanks in advance for any help

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,654 questions

1. 8,991 Reputation points
2021-08-05T04:53:40.757+00:00

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

1. 1,191 Reputation points
2021-08-05T00:51:42.857+00:00

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

2. 27,546 Reputation points
2021-08-05T06:27:53.517+00:00

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