Share via

DAX table search to apply discount based on ranges

Shinka 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:

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

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,689 questions
0 comments
Accepted answer
  1. Lz._ 8,991 Reputation points
    2021-08-05T04:53:40.757+00:00

    Hi @Shinka

    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

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Herbert Seidenberg 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

    0 comments
  2. Emily Hua-MSFT 27,601 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%")

    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.