Forum Discussion
SUMMARIZE Not Returning Correct Sum Value
Hello,
I'm trying to perform a SUM of the Production Qty at the Scheduled Shift level which is the next level of granularity from MATERIAL_NUMBER but I'm not getting the correct summation. I performing this summation so I can calculate a weighted average for a downstream calculation.
The screenshot below shows what should be happening. For Shift 1, the field "Production Qty (Date-PlantNum-...) is the measure that I'm using the SUMMARIZE function in to sum the Production Quantity at the Shift level. Instead of returning 1,541, the values being returned are at the MATERIAL_NUMBER level....653 and 888. The Weighted Average should then be .4237 and .576 instead of 1.0000.
Any guidance would be appreciated here.
Using the "Group By" functionality in Power Query Editor worked for me. I followed the instructions near the bottom of this article link...Link
7 Replies
Hi. I'm going to try and take a stab at it. What if you had the measure as something like...Production Qty = CALCULATE(SUM('Table'[Production Quantity]), ALLEXCEPT('Table', 'Table'[Scheduled Shift]))... where the values for 'Table' are whatever table you're sourcing from.The first 'Table' is the table that [Production Quantity] comes from, second 'Table' is that same table. The third 'Table' is the table from which [Scheduled Shift] is sourced from.As for the weighted average (this is more of a proportion or weighted average coefficient, but a nomenclature debate is out of scope), you just do,
Weight Av = DIVIDE([Production Quantity],[Production Qty])
...but based on your values of 1.000, it looks like you may already be doing something similar.
Hope this helps.