Calculate Discount Upto Dax

[vedika1089]

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

[v-jianpeng-msft]

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.

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.

[vedika1089]

Hello Li,

My desired result is different

Let me know if this helps.

Thank you

[Greg_Deckler · Accepted solution]

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