Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.