Forum Discussion

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 of Revenue

And so on.

Name	Total Visitor	Revenue	After Discount
x	100	5002	???
y	50	8773	???
z	500	76543	???

How to use 3 different conditions in one measure DAX.

Thanks,

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

3 Replies

PattemManohar
Community Champion
7 years ago
Anonymous Please try this..

AfterDiscount = VAR _discount = IF(VisitorRevenue[Total Visitor] <= 100, VisitorRevenue[Revenue] - VisitorRevenue[Revenue]*0.3, IF(VisitorRevenue[Total Visitor] <=200, VisitorRevenue[Revenue] - VisitorRevenue[Revenue]*0.5,VisitorRevenue[Revenue])) RETURN _discount

Note - I've used only two conditions as you mentioned 30% and 50% criteria, for anything else it will return same as Revenue (without any discount)
- 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
    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

Related content