Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a production plan table with hours required per product per line per week.
I want the hours per product as stacked columns for each week and the line as a slicer (this I can do).
Where I come unstuck is that I want to use the line series to show the total capacity available vs those stacked product hours bars.
The available hours per line per week comes from a separate table. When I try to combine the production plan required hours with the available hours I end up getting the available hours each week added for every product in the week. I've tried various ways of relating the tables and using DAX to calculate/combine but I can't get it to behave as required when slicing for different production lines/combinations.
Production table
Week | Product | Line | Required Hrs |
1 | Apple | A | 20 |
1 | Pear | A | 30 |
1 | Pinapple | B | 50 |
1 | Carrot | B | 60 |
1 | Ginger | C | 90 |
1 | Sausage | C | 80 |
2 | Apple | A | 10 |
2 | Pear | A | 70 |
2 | Pinapple | B | 40 |
2 | Carrot | B | 30 |
2 | Ginger | C | 80 |
2 | Sausage | C | 90 |
3 | Apple | A | 50 |
3 | Pear | A | 30 |
3 | Pinapple | B | 40 |
3 | Carrot | B | 60 |
3 | Ginger | C | 30 |
3 | Sausage | C | 90 |
Capacity Table
Week | Line | Available Hrs |
1 | A | 80 |
1 | B | 90 |
1 | C | 100 |
2 | A | 80 |
2 | B | 100 |
2 | C | 100 |
3 | A | 80 |
3 | B | 80 |
3 | C | 80 |
Desired output
Solved! Go to Solution.
Unfortunately, I am still not 100% sure I understand your problem but can you see if tweaking the model in the manner given will resolve the issue. Remember to use the Line and Week in any visual / tables from the Dimension (Line and Weeks) tables and not from the Fact (Production and Capacity table)
The desired output is not clear, do you want to show two bars for each week, one which shows the production and one which shows the capacity?
Thanks for seeking clarification - for each week (1,2,3 in this case) I would like a stacked bar of required hours for all products in that week. The capacity is then shown as a separate line series. Here's a chart with the full dataset that I have working in PowerBI - where it falls apart is that it adds together the capacity value for every product, rather than taking one value per line. I can get a workable result for one line by changing to use the average, but that doesn't work when I slice for multiple lines at the same time, because in that case I need the two different capacities added. In the example below you can see the capacity line is below the required hours in the first half of the chart (shortfall of capacity) and then above in the second half (excess capacity).
Currently I get capacity by looking up the hours for a given line in a given week from the capacity table. LineWk is just the line and the week concatenated to use as a lookup field.
LineWkHrs = LOOKUPVALUE(Capacity[Available Hours],Capacity[LineWk],'Extract'[LineWk])
And then in the production table I have a mesage that averages to workaround the duplication of avalable hours for every product, that is what is plotted via the "line values" optoin in the visualisation.
Capacity = CALCULATE(Average('Production'[LineWkHrs]))
I expect I have well over complicated this and gone off down a rabbit hole...
I think I can just append my capacity and production tables and then work directly on that, although there's versions of plans in the production one that add back some complexity...
Can you please share a snippet of "Model" view (as this may be where the fix needs to happen)?
Thanks for persevering!
I think the crux of the problem is I don't know how to prevent the capacity figures from being combined for every instance of a material in the production table. I want a stack of total production that I can have sliced by various product groupings from the lookup table and have that showing against a capacity figure that only combines for every instance of "Line".
Unfortunately, I am still not 100% sure I understand your problem but can you see if tweaking the model in the manner given will resolve the issue. Remember to use the Line and Week in any visual / tables from the Dimension (Line and Weeks) tables and not from the Fact (Production and Capacity table)
Yes, I needed to use the weeks/line from the dimention table. So simple after going down a rabbit whole of DAX formulae... Well, I've learnt a lot of new tricks and you've taught me a key fundamental. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |