cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
malshaikh7
Frequent Visitor

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 VisitorRevenue After Discount 
x1005002???
y508773???
z50076543

???

 

 

How to use 3 different conditions in one measure DAX. 

Thanks, 

 

 

1 ACCEPTED SOLUTION

hi, @malshaikh7

You can try to this way,

Step1:

Create a discount fact table

7.PNG

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:

 

8.PNG

 

here is pbix, please try it.

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

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
PattemManohar
Community Champion
Community Champion

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

image.png

 

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)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you.

 

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

 

 

hi, @malshaikh7

You can try to this way,

Step1:

Create a discount fact table

7.PNG

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:

 

8.PNG

 

here is pbix, please try it.

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

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!