Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help with DAX discount

Dear All,   I need help to subtract amount based on discount criteria as:    Example:    Total visitor : 1-100 = take out 30% out of Revenue Total Visitor in range 100-200 = take out 50% out o...
Show and Tell
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous

    You can try to this way,

    Step1:

    Create a discount fact table

    Step2:

    Add a column that to judge how much discount will apply for current row

     

    discount = CALCULATE(MAX(Discount[discount]),FILTER(Discount,Table1[Total Visitor]>Discount[start]&&Table1[Total Visitor]<=Discount[end]))

     

    Note: MAX(Discount[discount]) is to prevent there two or more rows with the same start column and end column.

    Step3:

    add result column

     

    Result = IF(ISBLANK(Table1[discount]),Table1[Revenue ],Table1[Revenue ]*(1-Table1[discount]))

    Result:

     

     

    here is pbix, please try it.

    https://www.dropbox.com/s/30ylt6a7j0u7bpu/Help%20with%20DAX%20discount.pbix?dl=0

     

    Best Regards,

    Lin

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you.

 

Is there anyway else to change the percent discount instead of edit measure every time I want to change the discount % number.  

 

 

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous

You can try to this way,

Step1:

Create a discount fact table

Step2:

Add a column that to judge how much discount will apply for current row

 

discount = CALCULATE(MAX(Discount[discount]),FILTER(Discount,Table1[Total Visitor]>Discount[start]&&Table1[Total Visitor]<=Discount[end]))

 

Note: MAX(Discount[discount]) is to prevent there two or more rows with the same start column and end column.

Step3:

add result column

 

Result = IF(ISBLANK(Table1[discount]),Table1[Revenue ],Table1[Revenue ]*(1-Table1[discount]))

Result:

 

 

here is pbix, please try it.

https://www.dropbox.com/s/30ylt6a7j0u7bpu/Help%20with%20DAX%20discount.pbix?dl=0

 

Best Regards,

Lin