Individual Slicer for each row of a table

[joberg]

I'm looking to build out a product costing model for my organization and I'm having trouble building out the raw materials portion of the model with the same flexibility that excel would afford me.

The current data source loaded into Power BI supplies the Part #, Description, Kg/Batch, and the 3 month average cost, 6 month average cost, 12 month average cost, last cost and standard cost. What I would like to incorporate into the Power BI report is a slicer (or any other user input variable) where by product I can select whether to use: the 3 month average cost, 6 month average cost, 12 month average cost, last cost or standard cost, and then have a custom measure that calculates the extended cost for that product.

In excel the table would look something like this (where the 'Cost Select' is a dropdown selection):

Part #	Description	Kg/Batch	3M Avg Cost	6M Avg Cost	12M Avg Cost	Last Cost	Std Cost	Cost Select	Ext Cost
1-xxxx	Brown Sugar	50	5.00	6.00	7.00	8.00	8.00	3M Avg Cost	250.00
2-xxxx	Oats	50	5.00	6.00	7.00	8.00	8.00	6M Avg Cost	300.00
3-xxxx	Chia Seed	50	5.00	6.00	7.00	8.00	8.00	12M Avg Cost	350.00
4-xxxx	Flour	50	5.00	6.00	7.00	8.00	8.00	Last Cost	400.00
5-xxxx	Peanut Butter	50	5.00	6.00	7.00	8.00	8.00	Std Cost	400.00

I realize that the slicer or user input varialbe for 'Cost Select' probably cannot be in-line in the table, I was trying to put a slicer for each row over a dummy column in the table and using a switch statement to calculate the Ext Cost. I contemplated creating parameters in Power Query, but I would like the ability to change the variables on the report itself so that as we're looking at the data points we can make the decision on which Cost Scenario to use. I cannot figure it out nor find a forum in the user community to help me solve this issue. 

Help is much appreciated!

[v-luwang-msft · Accepted solution]

HI @joberg ,

Test the below :

Solution1,create a slicer table:

Then create the below measure:

Final = 
SWITCH (
    SELECTEDVALUE ( Slicer[Slicer] ),
    "3M Avg Cost", 5 * MAX ( 'Table'[Kg/Batch] ),
    "6M Avg Cost", 6 * MAX ( 'Table'[Kg/Batch] ),
    "12M Avg Cost", 7 * MAX ( 'Table'[Kg/Batch] ),
    "Last Cost", 8 * MAX ( 'Table'[Kg/Batch] ),
    "Std Cost", 8 * MAX ( 'Table'[Kg/Batch] )
)

Solution2,unpovit table:

Then create new column:

sum = Table2[Kg/Batch]*Table2[Value]

visual:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[joberg]

 Hi Lucien,

In the table below the 'Ext Cost' is the result column, it is multiplying the respective cost column using the 'Cost Select' value (which is the slicer value) by the Kg/Batch column. 

If you look at the first row, the 'Cost Select' is '3M Avg Cost', so the math is 50 x 5 = 250.

Hope this helps!

[v-luwang-msft]

Hi @joberg ，

Is it possible to show the results you want to get when you use different slicers?

Best Regards

Lucien

[joberg]

Here is a screenshot of what the data looks like in excel, apologies for not providing in the OP.