cancel
Showing results for
Did you mean:
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

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

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

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

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.
Regular Visitor

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?

Community Support Team

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.
Regular Visitor

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!