Help with DAX discount

[Anonymous]

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, 

[PattemManohar]

@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]

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 · Accepted solution]

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