March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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!
Solved! Go to 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
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
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!
Hi @joberg ,
Is it possible to show the results you want to get when you use different slicers?
Best Regards
Lucien
Here is a screenshot of what the data looks like in excel, apologies for not providing in the OP.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
92 | |
86 | |
77 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |