Forum Discussion

Brysonds's avatar
Brysonds
Icon for Helper III rankHelper III
8 years ago

Help wanted: Applying what-if calculation to specific rows based on slicer selection

Hi!

 

Before I jump into the detail, here is a high-level of what I am trying to do:

 

I want to see the impact on a final product's cost when a material that goes into that product increases.

 

For example, Water goes into Gatorade. If the cost of water increases, what will be impact on the price of Gatorade? 

 

Here is what I have so far:

 

1) A slicer for "material" --- this is where I select water

2) A slicer for "product" --- this list is filtered to only show products that use water as a material

3) A what-if slider to input a simulated material cost increase/decrease

4) A table showing product, material, material component, actual cost, and a "simulated cost" based on a what-if calculation

 

Where I need help:

 

My table has an interaction with the "product" slicer I mentioned, but not with the "material" slicer. This works great as it shows me the detail based on the product(s) I select without removing the other "material's that go into that product. However, while I DO want to see all of these rows, I only want to apply the "simulated cost" calculation from the what-if to certain rows in the table.

 

Here is where it gets tricky. I only want the what-if calculation applied to the rows that have a material equal to the material that was selected in the slicer AND where the material component is a static value of "raw material".

 

For example:

 

Material slicer = Water

Simulated Cost (from what-if slider) = 1.20 <----  meaning 20% increase

 

Here is how I want the table to look. Note that the simulated cost only changed in row 2 (because material = slicer material & material component = raw material). The costs just carried over and didn't change based on the slider.

 

ProductMaterialMaterial ComponentCostSimulated Cost
GatoradeWaterLabor1.501.50
GatoradeWaterRaw Material1.001.20
GatoradeSodiumRaw Material0.700.70
  Total3.203.40

 

The table now shows me that the impact of water increasing in cost by 20% will have a total impact on my product cost of $ 0.20 cents. 

 

Here is the simulated cost formula I am trying to use, but it isn't working. 

 

Simulated Cost =
VAR MaterialSelection = SELECTEDVALUE(FG[Material])

RETURN

SUMX(FILTER('FG','FG'[Material Component]="Raw Material" && FG[Material]=MaterialSelection), FG[Cost] * 'Parameter 1'[Slider Value])
+
SUMX(FILTER('FG','FG'[Material Component]<>"Raw Material"), 'FG'[Cost])

 

Here is where it breaks

 

I am attempting to use the variable to only apply the sumx calculation when the material in the row equals the material in the slicer. This doesn't work. It applies the calculation to all rows where it's a "raw material" instead of just the rows with the matching slicer value (water).

 

FYI - the 2nd sumx calculation to simply carry over the original cost when the value is not "raw material". (See row 1 and row 3 simulated cost above in my table). This works fine. 

 

ProductMaterialMaterial ComponentCostSimulated Cost
GatoradeWaterLabor1.501.50
GatoradeWaterRaw Material1.001.20
GatoradeSodiumRaw Material0.700.84
  Total3.203.54

 

Any ideas?

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Brysonds,

      How do you get the following row when you select water in Material slicer? The row should be filtered out and you are unable to see it in table visual.

      Gatorade Sodium Raw Material 0.70 0.84



      Regards,
      Lydia

      • Brysonds's avatar
        Brysonds
        Icon for Helper III rankHelper III

        Hi Lydia, 

         

        I am able to see the additional rows because I turned off the filtering interaction between the 1st slicer (where I selected water) and the table that contains all the rows.

         

        This is done by selecting the slicer, then clicking the Format tab on the ribbon, then selecting Edit Interactions. Lastly, you click the circle icon on the table which "turns off" the filtering from the slicer.