cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joberg
Frequent Visitor

Individual Slicer for each row of a table

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 #DescriptionKg/Batch3M Avg Cost6M Avg Cost12M Avg CostLast CostStd CostCost SelectExt Cost
1-xxxxBrown Sugar50               5.00               6.00                7.00               8.00               8.003M Avg Cost    250.00
2-xxxxOats50               5.00               6.00                7.00               8.00               8.006M Avg Cost    300.00
3-xxxxChia Seed50               5.00               6.00                7.00               8.00               8.0012M Avg Cost    350.00
4-xxxxFlour50               5.00               6.00                7.00               8.00               8.00Last Cost    400.00
5-xxxxPeanut Butter50               5.00               6.00                7.00               8.00               8.00Std 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!

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

HI @joberg ,

Test the below :

Solution1,create a slicer table:

vluwangmsft_0-1655865455492.png

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] )
)

vluwangmsft_1-1655865806640.png

 

Solution2,unpovit table:

vluwangmsft_2-1655865845226.png

Then create new column:

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

visual:

vluwangmsft_3-1655866050042.png

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


Best Regards

Lucien

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

HI @joberg ,

Test the below :

Solution1,create a slicer table:

vluwangmsft_0-1655865455492.png

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] )
)

vluwangmsft_1-1655865806640.png

 

Solution2,unpovit table:

vluwangmsft_2-1655865845226.png

Then create new column:

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

visual:

vluwangmsft_3-1655866050042.png

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


Best Regards

Lucien

joberg
Frequent Visitor

 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. 


Capture.PNG
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
Community Support
Community Support

Hi @joberg ,

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

 

 

Best Regards

Lucien

joberg
Frequent Visitor

joberg_0-1655495300888.png

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.