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 s...
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

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

    • shutevd's avatar
      shutevd
      Frequent Visitor
      5 years ago

      Thanks Samarth_18 , what I meant is in the end result we want to see the values that increaseby based on the multiple and TrademarkName selection, everybody else to keep their current values. So for example C,D,E,F,G should remain the same, A and B should have the price increase.

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

        Okay shutevd got it. Please refer below code for it :-

        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,
        MAX ( 'BI'[SalesPreTax] )
    )

        Output:-

        Thanks,

        Samarth