cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Calculate Discount Upto Dax

Hello,
I am trying to write a Dax where I can Filter the page based on the selected discount. I want see the all discounted products details based on the discount selected .
That is if I select 20%, I want to see all the products that are discounted up to 20%

Firstly I made a table with these discount fields to use as a filter. Then I created a calculated Column in my Main Table using the Dax below

PercentageGroup =
VAR Percentage = Table[%Discount]
RETURN
SWITCH(
TRUE(),
Discount< -100, "100% or More",
Discount< -90, "90%",
Discount< -80, "80%",
Discount< -70, "70%",
Discount< -60, "60%",
Discount< -50, "50%",
Discount < -40, "40%",
Discount<= -30, "30%",
Discount<= -20, "20%",
Discount<= -10, "10%"
Discount= 0, "No Change (0%)",
Discount>= 1 , "Above Retail",
"Invalid"
)

The problem with this Dax is that when I select 20%, I get resullt ranging from 20 % to 30 % discount. Whereas I need it to be all up to 20 % off. Similarly if I select 30% , I want the result to be all products up to 30% off.

Thank you
1 ACCEPTED SOLUTION
Super User

@vedika1089 I would recommend a disconnected table for your slicer and then a Complex Selector measure: The Complex Selector - Microsoft Fabric Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Community Support

Hi, @vedika1089

Based on your description, I created the following sample data:

Table:

Discount table:

I use the following DAX to calculate Sales under each discount:

``````Discount Sales =
VAR _discount =
SELECTEDVALUE ( Discount[Discount] )
RETURN
IF (
_discount = BLANK (),
CALCULATE ( SUM ( 'Table'[Sales] ) ),
CALCULATE ( SUM ( 'Table'[Sales] ) * _discount )
)
``````

Place the product and this measure in the table visual, and place the discount field of the Discount table in the slicer:

When selecting different discounts, the corresponding sales will be calculated, and the results are as follows:

I have provided the PBIX file used this time. I hope it will be helpful to you.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

Frequent Visitor

Hello Li,

My desired result is different

Let me know if this helps.

Thank you

Super User

@vedika1089 I would recommend a disconnected table for your slicer and then a Complex Selector measure: The Complex Selector - Microsoft Fabric Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...