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 differe...
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,

     

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,

       

      • acbg's avatar
        acbg
        Resolver III
        6 years ago

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