Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Brysonds
Helper III
Helper III

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
v-cherch-msft
Microsoft Employee
Microsoft Employee

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.

View solution in original post

4 REPLIES 4
v-cherch-msft
Microsoft Employee
Microsoft Employee

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.

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

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.

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.