Forum Discussion
Line and Stacked Column Chart - Totalised scheduled production hours vs a capacity 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)
Continued ContributorThe 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...
- moizsherwani
Can you please share a snippet of "Model" view (as this may be where the fix needs to happen)?
Thanks for persevering!
- The production table has various values per material per receipt wk. (i.e. product per week)
- The Total Receipts is divided by the rate (ctn/hr) for the material in the Lookups table to get the required hours for that material that week. (as of this snapshot I have the information calculated in PowerQuery and stored as the PrdnHrs column).
- The capacity table has values of available capacity per line per week.
- Lookups table is joined to Production by material ID.
- Capacity table is joined to Production by LineWk
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".
- moizsherwani
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)