Forum Discussion

acbg's avatar
acbg
Resolver III
6 years ago
Solved

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.

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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)

     

     

     

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

     

     

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

     

     

    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)

     

     


    Best regards,

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • acbg's avatar
      acbg
      Resolver III
      6 years ago

      Hey Anonymous 

       

      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. 

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        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)

         

         

         

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

         

         

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

         

         

        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)

         

         


        Best regards,

         

  • Dips_PWBI's avatar
    Dips_PWBI
    New Member
    3 years ago

    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?