Forum Discussion
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.
| Product | Material | Material Component | Cost | Simulated Cost |
| Gatorade | Water | Labor | 1.50 | 1.50 |
| Gatorade | Water | Raw Material | 1.00 | 1.20 |
| Gatorade | Sodium | Raw Material | 0.70 | 0.70 |
| Total | 3.20 | 3.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.
| Product | Material | Material Component | Cost | Simulated Cost |
| Gatorade | Water | Labor | 1.50 | 1.50 |
| Gatorade | Water | Raw Material | 1.00 | 1.20 |
| Gatorade | Sodium | Raw Material | 0.70 | 0.84 |
| Total | 3.20 | 3.54 |
Any ideas?
6 Replies
- Brysonds
Helper III
"edit"
- AnonymousNot 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
Helper 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.