What-if calculation based on selected value in slicer not working - please help!

[Brysonds]

Hi,

I have a hopefully simple problem.

I want to apply a simple calculation to my table based on a value from a what-if parameter.

However, I want this calculation to only be applied to certain rows in my table:

The first condition is that the column Cost Price Component = "Material"

The second condition is that the column Material Component = the selected value in a slicer called Material Component

I tried the following formula:

Sim Cost 1 =
VAR SlicerValue = SELECTEDVALUE(FG[Material Component])

RETURN

SUMX(FILTER('FG','FG'[Cost Price Component]="Material" && FG[Material Component]=SlicerValue), FG[Cost] * (1+Parameter[Parameter Value]))

The calculation is correct, the problem is that it is is applying the calculation to the wrong rows.

Based on my formula, I expect the calculation to only be applied to Row 5 below (this is the only row where the cost price component is material AND the material component slicer is acetic acid). However, it is applying to all rows where cost price component is material, ignoring the 2nd part of the statement. 

[v-cherch-msft · Accepted solution]

Hi @Brysonds

You may try to new a table with DISTINCT Function to get the ‘Material Component’ column. Then use the column as Slicer. Change your measure as below:

Sim Cost 1 =
VAR SlicerValue =
    SELECTEDVALUE ( NewTable[Material Component] )
RETURN
    SUMX (
        FILTER (
            'FG',
            'FG'[Cost Price Component] = "Material"
                && FG[Material Component] = SlicerValue
        ),
        FG[Cost]
            * ( 1 + Parameter[Parameter Value] )
    )

For example:

Regards,

Cherie

[Brysonds]

Hi Cherie,

Thank you so much for the reply. I must be missing something -  I tried creating your simple example with name, group, value and that also didn't work for me. Could you share your code for the "test" measure you created?

[v-cherch-msft]

Hi @Brysonds

You may refer to below:

test =
VAR slicervalue =
    SELECTEDVALUE ( 'Table 2'[Name] )
RETURN
    SUMX (
        FILTER ( Table2, Table2[Group] = "a1" && Table2[Name] = slicervalue ),
        Table2[value] * Parameter[Parameter Value]
    )

Regards,

Cherie

[Brysonds]

Hi Cherie,

Thank you for all your help.

I figured out what I was doing wrong 🙂

I was manually creating the second table and entering in unique values for "Name". Instead, I needed to create a new table and use the DISTINCT function --- I missed this part in your original post. 

Table 2 = DISTINCT(Table1[Name])

It is now working!

Take care!