Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
vedika1089
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%

vedika1089_0-1709312440642.png

 

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
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
v-jianpeng-msft
Community Support
Community Support

Hi, @vedika1089 

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

Table:

vjianpengmsft_0-1709541103108.png

Discount table:

vjianpengmsft_1-1709541129945.png

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:

vjianpengmsft_2-1709541360925.png

vjianpengmsft_3-1709541373194.png

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

vjianpengmsft_4-1709541438352.png

vjianpengmsft_5-1709541454223.png

vjianpengmsft_6-1709541474154.png

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

 

 

 

How to Get Your Question Answered Quickly

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.

 

Hello Li,

My desired result is different

vedika1089_0-1709561045678.png

 

 

Let me know if this helps.

 

Thank you

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.