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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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