Forum Discussion

shutevd's avatar
shutevd
Frequent Visitor
5 years ago
Solved

Ignore Slicer and multiply by selected value

Hi guys,

 

I am struggling with the following scenario:

I have a table that contains product names, next to it I have a column that contains the Sales before Tax. My client wants to be able to select a product and then select price increase i.e. mutiply the price by 2,3, etc and see how this will reflect on all products inside the table. My issue is that once I use a slicer for product name, the whole table gets sliced to show only the selected product. I would like to be able to select a product from Slicer A, then use another Slicer to select how much I want to multiply the sales for the selected product with, but the rest of the unselected products should remain visible inside the table only the selected product should get a price increase. I tried using something like this, but unfortunately it does not do the trick for me. Does anyone have any ideas?

 

 

 

var selectedval = SELECTEDVALUE(BI[TrademarkName])
RETURN
IF(ISFILTERED(BI[TrademarkName]), CALCULATE([SalesPreTax], ALL(BI[TrademarkName]), BI[TrademarkName]=selectedval), [SalesPreTax])

 

 

 



Need Help
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    Hi shutevd ,

     

    Please follow below steps:-

     

    1. Remove relation between trademarkdummy and factsales.

    2. Add trademarks from trademarkdummy table into the slicer

    3. Now update measure code as below:-

     

    MultipliedProfit =
VAR selected_value =
    SELECTEDVALUE ( TrademarksDummy[TradeMark] )
VAR mutiple =
    SELECTEDVALUE ( Multiplier[Value] )
RETURN
    IF (
        NOT ( ISBLANK ( selected_value ) ) || NOT ( ISBLANK ( mutiple ) ),
        IF (
            MAX ( factSales[TradeMark] ) IN VALUES ( TrademarksDummy[TradeMark] ),
            [ProfitPreTax] * mutiple,
            [ProfitPreTax]
        ),
        [ProfitPreTax]
    )

     

    You will see below output:-

    And for getting correct total, you can create a another measure as below:-

    with_Correct_Total =
SUMX (
    SUMMARIZE (
        factSales,
        factSales[TradeMark],
        "total", Multiplier[MultipliedProfit]
    ),
    [total]
)

    you can use it instead of MultipliedProfit measure but dont delete MultipliedProfit measure

     

    Thanks,

    Samarth

14 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi shutevd ,

     

    To achieve this you can follow below steps:-

     

    1. Create a seperate table for all your product and put that into slicer.

     

     

    BI_Distinct_Trademark = DISTINCT('BI'[TrademarkName])

     

     

     

    2. Create a table to select how much you want to multiply and add it to another slicer

     

    3. Now create a measure and add in to your visual.

     

     

    IncreasedBy =
VAR selected_value =
    SELECTEDVALUE ( BI_Distinct_Trademark[TrademarkName] )
VAR multiple =
    SELECTEDVALUE ( MultipleBy[Multiple] )
RETURN
    IF (
        MAX ( 'BI'[TrademarkName] ) = selected_value,
        MAX ( 'BI'[SalesPreTax] ) * multiple,
        BLANK ()
    )

     

     

     

    You will below as result:

     

    Thank you,

    Samarth

     

    • shutevd's avatar
      shutevd
      Frequent Visitor
      5 years ago

      Thank you, Samarth_18, the solution solves part of the problem, but what if I need to keep the rest of the values as well inside the IncreaseBy measure? Let's say I want to multiply A,B by 2, but C,D,E, etc should also appear inside the values. It is almost like a what if analysis, what if we increase the price of products A and B, and see how this will change the sales for the rest of the products. How can I achieve that?

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Okay shutevd , you can update code as below.

         

        IncreasedBy =
VAR multiple =
    SELECTEDVALUE ( MultipleBy[Multiple] )
VAR _maxx =
    MAX ( 'BI'[TrademarkName] )
RETURN
    IF (
        MAX ( 'BI'[TrademarkName] ) IN VALUES ( BI_Distinct_Trademark[TrademarkName] ),
        MAX ( 'BI'[SalesPreTax] ) * multiple,
        BLANK ()
    )

         

        Output:-

        Thanks,

        Samarth