Ignore Slicer and multiply by selected value

[shutevd]

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])

[shutevd]

Thank you so much for your hard work @Samarth_18 , it works!

[Samarth_18]

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]

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]

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]

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]

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]

@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?

[Samarth_18]

If you drag yor TaxBeforeProfit  measure only into your visual so is it giving you some data?

[shutevd]

It is, yes, it is a SUMX function that is calculating the result.

[Samarth_18]

And I hope you are refering your measure in our measure code like this:-

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

[shutevd]

I am indeed, however I don't get results for the rest because of the IF statement, it says if the Value is in the Selected Values then multiply the SalesBeforeTax by the multiplicator, else just give us the SalesBeforeTax. And this is where it gets tricky, because DAX only returns values for the filtered Trademarks.

[Samarth_18]

Something is really missing in between. Is it possible for you to share PBIX file you can remove sensitive data and only keep what is enough to solve the above problem.

[shutevd]

@Samarth_18 , please find attached the pbix file 

https://easyupload.io/jymwa2 

[Samarth_18 · Accepted solution]

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