Forum Discussion
Calculation based on selected slicer
Hi,
I want to calculate the remaining capacity of ovens based on which calendar week is selected. Also keep in mind that multiple products can have pieces in one oven at the same the if the capacity allows it.
The scenario is for tracking the capacity of different Ovens in which pieces are stressed over a period of time.
Simplified Code:
Filtered by selected calendar week
FOR (Count = 1; Count <= Number of same 'Oven ID'; Count++)
'Remaining Capacity' = Capacity - 'Pieces' of Count
How I want it to work is that I select a calendar week in the already working slicer, then check if multiple projects have pieces in the same oven and in both cases then calculate the remaining capacity of the oven.
Any help is greatly appreciated
4 Replies
Can you share sample data and sample output.
I have an oven named 'Sierra' with a capacity of 128. I have two projects 'Project1' and 'Project2'. 'Project1' has 80 pieces to stress whereas 'Project2' has 20 pieces to stress. Both projects are to be stressed in the 'Sierra' oven during the calendar week three.
Right now I made a calculated column in my 'Stressing' table with this formula:
Remaining Capacity = RELATED(Oven[Capacity]) - Stressing[Pieces]
But for the aforementioned scenario the remaining capacity will be wrong because the 'Remaining capacity' will be for 'Project1' = 48 and for 'Project2' = 108.
So am guessing that I need a measure that checks the names of the ovens during the selected week and if there are multiple projects in one oven then substracts the number of pieces of the projects from the capacity of the oven.
'Remaining capacity' = 'Capacity Oven Sierra' - 80('Number of Pieces of Project1') - 20('Number of Pieces of Project2')
'Remaining capacity' = 28I hope this is what you meant by sample data and sample output if not please let me know.
Anonymous
Sample data and sample output he meant is the data tables and screen shorts of expected results. This could help us understand your requirement.
Read this post to get your answer quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Best
Relationships
The relationships between the tables. 'Stressing(referenced)' is a table I made to expand the dates between 'start date' and 'end date' of 'Stressing'.Relevant table data
ID Oven ID Product Start Date Pieces Duration in Days Status End Date 17 Charlie 10 09.03.2020 96 49 Final Qual 27.04.2020 18 Charlie 11 09.03.2020 32 49 Final Qual 27.04.2020 ID Oven Capacity 1 Charlie 128 amitchandak
I made a calculated column called 'Remaining Capacity' in the Stressing table which you can see in the right visual
'Remaining Capacity' = RELATED(Oven[Capacity]) - Stressing[Pieces]
I'd like to have a solution which checks if there are multiple projects in the same oven during the same week and then calculates the remaining capacity depending on the pieces of all the projects in this oven.
Example with data from the visual:
'Remaining Capacity' = 128(Capacity of the oven Charlie) - 96(Pieces of the first project) - 32(Pieces of the second project)
'Remaining Capacity' = 0
