cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brysonds Regular Visitor
Regular Visitor

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

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. 

 

Capture.JPG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

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

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:

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Highlighted
Community Support Team
Community Support Team

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

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:

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Brysonds Regular Visitor
Regular Visitor

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

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?

 

 

Capture.JPG

Community Support Team
Community Support Team

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

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Brysonds Regular Visitor
Regular Visitor

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

Hi Cherie,

 

Thank you for all your help.

 

I figured out what I was doing wrong Smiley Happy

 

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!