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

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

      • shutevd's avatar
        shutevd
        Frequent Visitor
        5 years ago

        Samarth_18 thank you, however the MAX function does not accept DAX formulas, since my TaxBeforeProfit is coming from a Measure, if I just reference the measure instead of the MAX function, it doesn't return anything. How do I use the MAX with Measure?