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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
acbg
Resolver III
Resolver III

What if Parameter multiple parameters on same column

Users want the ability to do what if scenario on rebate percentage on multiple customers at the same time.

So Customer A would 5% rebate percentage, Customer B would be 7% and so on for 30 different customers.

Once they enter these percentages then they can see how it impacts the Total Actual Sales.


Total Actual Sales = Sales - Rebates


Not sure how to capture multiple what if parameters based on each customer at the same time.

Any ideas would be appreciated.

1 ACCEPTED SOLUTION

Hi @acbg ,

 

If some customer has same rebate value, we can create some parameter with a customer select slicer to reduce the number of parameter

 

1. create some parameter for each rebate value ( for example , we create five kind of rebate table)

 

3.jpg

 

 

2. create some customer calculated table, they have same formula but have different name

 

 

Customer of Rebate 1 = DISTINCT('Table'[Customer Name])

 

 

4.jpg5.jpg

3. create a measure to calculate the total sales

Total Sales After Rebate = 
IF (
    ISFILTERED ( 'Customer of Rebate 1'[Customer Name] ),
    CALCULATE (
        SUM ( 'Table'[Sales] ) * ( 1 - [Rebate 1 Value] ),
        'Table'[Customer Name]
            IN FILTERS ( 'Customer of Rebate 1'[Customer Name] )
    ),
    BLANK ()
)
    + IF (
        ISFILTERED ( 'Customer of Rebate 2'[Customer Name] ),
        CALCULATE (
            SUM ( 'Table'[Sales] ) * ( 1 - [Rebate 2 Value] ),
            'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 2'[Customer Name] )
        ),
        BLANK ()
    )
    + IF (
        ISFILTERED ( 'Customer of Rebate 3'[Customer Name] ),
        CALCULATE (
            SUM ( 'Table'[Sales] ) * ( 1 - [Rebate 3 Value] ),
            'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 3'[Customer Name] )
        ),
        BLANK ()
    )
    + IF (
        ISFILTERED ( 'Customer of Rebate 4'[Customer Name] ),
        CALCULATE (
            SUM ( 'Table'[Sales] ) * ( 1 - [Rebate 4 Value] ),
            'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 4'[Customer Name] )
        ),
        BLANK ()
    )
    + IF (
        ISFILTERED ( 'Customer of Rebate 5'[Customer Name] ),
        CALCULATE (
            SUM ( 'Table'[Sales] ) * ( 1 - [Rebate 5 Value] ),
            'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 5'[Customer Name] )
        ),
        BLANK ()
    )
    + CALCULATE (
        SUM ( 'Table'[Sales] ),
        IF (
            ISFILTERED ( 'Customer of Rebate 1'[Customer Name] ),
            NOT 'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 1'[Customer Name] ),
            TRUE ()
        ) && IF (
            ISFILTERED ( 'Customer of Rebate 2'[Customer Name] ),
            NOT 'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 2'[Customer Name] ),
            TRUE ()
        )&& IF (
            ISFILTERED ( 'Customer of Rebate 3'[Customer Name] ),
            NOT 'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 3'[Customer Name] ),
            TRUE ()
        )&& IF (
            ISFILTERED ( 'Customer of Rebate 4'[Customer Name] ),
            NOT 'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 4'[Customer Name] ),
            TRUE ()
        )
        && IF (
            ISFILTERED ( 'Customer of Rebate 5'[Customer Name] ),
            NOT 'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 5'[Customer Name] ),
            TRUE ()
        )
    )

 

Hi @acbg ,

 

If some customer has same rebate value, we can create some parameter with a customer select slicer to reduce the number of parameter

 

1. create some parameter for each rebate value ( for example , we create five kind of rebate table)

 

3.jpg4.jpg5.jpg6.jpg

 


Best regards,

 

Community Support Team _ Dong Li
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

6 REPLIES 6
Dips_PWBI
New Member

This is exactly what Iv ebeen looking for however @acbg how can I incorporate if I needed to calculate the average instead of the total?

Ie Instead of Total Sales after Rebate, I want to calculate the Avg Price per unit after rebate?

Tad17
Solution Sage
Solution Sage

Hey @acbg 

 

My first thought is to simplify this and create a slicer for each customer with the various percentages, let the users select a discount percentage for each customer in the various slicers and have the end result be shown in your table. The only issue this is simplest if you have calculated tables for each customer/discount percentage. not hard to create each table using DAX or using measures, just some busy work. For other more advanced What If concepts see the links below:

 

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

 

https://community.powerbi.com/t5/Community-Blog/What-If-Analysis-Techniques-For-Power-BI/ba-p/460414

Hey @Tad17 

 

Thanks for the idea, thats what I was beginning to think as well. To create seperate parameters for each customer and create a measure for each. 

Was wondering if there is a more simpler way to do this in power bi. 

Hi @acbg ,

 

If some customer has same rebate value, we can create some parameter with a customer select slicer to reduce the number of parameter

 

1. create some parameter for each rebate value ( for example , we create five kind of rebate table)

 

3.jpg

 

 

2. create some customer calculated table, they have same formula but have different name

 

 

Customer of Rebate 1 = DISTINCT('Table'[Customer Name])

 

 

4.jpg5.jpg

3. create a measure to calculate the total sales

Total Sales After Rebate = 
IF (
    ISFILTERED ( 'Customer of Rebate 1'[Customer Name] ),
    CALCULATE (
        SUM ( 'Table'[Sales] ) * ( 1 - [Rebate 1 Value] ),
        'Table'[Customer Name]
            IN FILTERS ( 'Customer of Rebate 1'[Customer Name] )
    ),
    BLANK ()
)
    + IF (
        ISFILTERED ( 'Customer of Rebate 2'[Customer Name] ),
        CALCULATE (
            SUM ( 'Table'[Sales] ) * ( 1 - [Rebate 2 Value] ),
            'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 2'[Customer Name] )
        ),
        BLANK ()
    )
    + IF (
        ISFILTERED ( 'Customer of Rebate 3'[Customer Name] ),
        CALCULATE (
            SUM ( 'Table'[Sales] ) * ( 1 - [Rebate 3 Value] ),
            'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 3'[Customer Name] )
        ),
        BLANK ()
    )
    + IF (
        ISFILTERED ( 'Customer of Rebate 4'[Customer Name] ),
        CALCULATE (
            SUM ( 'Table'[Sales] ) * ( 1 - [Rebate 4 Value] ),
            'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 4'[Customer Name] )
        ),
        BLANK ()
    )
    + IF (
        ISFILTERED ( 'Customer of Rebate 5'[Customer Name] ),
        CALCULATE (
            SUM ( 'Table'[Sales] ) * ( 1 - [Rebate 5 Value] ),
            'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 5'[Customer Name] )
        ),
        BLANK ()
    )
    + CALCULATE (
        SUM ( 'Table'[Sales] ),
        IF (
            ISFILTERED ( 'Customer of Rebate 1'[Customer Name] ),
            NOT 'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 1'[Customer Name] ),
            TRUE ()
        ) && IF (
            ISFILTERED ( 'Customer of Rebate 2'[Customer Name] ),
            NOT 'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 2'[Customer Name] ),
            TRUE ()
        )&& IF (
            ISFILTERED ( 'Customer of Rebate 3'[Customer Name] ),
            NOT 'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 3'[Customer Name] ),
            TRUE ()
        )&& IF (
            ISFILTERED ( 'Customer of Rebate 4'[Customer Name] ),
            NOT 'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 4'[Customer Name] ),
            TRUE ()
        )
        && IF (
            ISFILTERED ( 'Customer of Rebate 5'[Customer Name] ),
            NOT 'Table'[Customer Name]
                IN FILTERS ( 'Customer of Rebate 5'[Customer Name] ),
            TRUE ()
        )
    )

 

Hi @acbg ,

 

If some customer has same rebate value, we can create some parameter with a customer select slicer to reduce the number of parameter

 

1. create some parameter for each rebate value ( for example , we create five kind of rebate table)

 

3.jpg4.jpg5.jpg6.jpg

 


Best regards,

 

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

This is incredible stuff, great job. 
Would you by any chance know a DAX solution for how to calculate the total sales after rebate per client? So basically the same as above, but with the ability to show the amounts for each client

@v-lid-msft thanks thats where I ended up doing as well. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.