Forum Discussion
PowerBI calculate average (sumproduct) shows error data
Try like
Col_NVL = CALCULATE(
divide(sumx('NVL','NVL'[actual qty]*'NVL'[Total]),sum(NVL[actual qty])),ALLEXCEPT('NVL',NVL[Line.]),FILTER(,'NVL'[Week]<=max('NVL'[Week])))
move all expect outside filter.
Super UserTry like
Col_NVL = CALCULATE(
divide(sumx('NVL','NVL'[actual qty]*'NVL'[Total]),sum(NVL[actual qty])),ALLEXCEPT('NVL',NVL[Line.]),FILTER(,'NVL'[Week]<=max('NVL'[Week])))
move all expect outside filter.
Thank you, amitchandak,
Yes, your answer is okay for Line summary, but when I go to upper level, workshop, it's wrong, as below:
Every line average is right, but when summarize to upper level, average (0,18,0,32,0), it sould be 10.16, but it shows 5.22. so error. data.
Workshop 202014 W 5.22 Line29 0.00 Line12Q 18.00 Line12N 0.00 Line30 32.80 Line131 0.00 I am trying to load raw data, but failed, because of too many characters.
So Here just some of whol raw data, for your information.
Workshop Line No. actual qty Total Week Year WeekNum W Line 131 128721 0 WK52 2019 201952 W Line 131 128721 0 WK51 2019 201951 W Line 131 128721 0 WK50 2019 201950 W Line 131 128721 0 WK49 2019 201949 W Line12N 12000 0 WK14 2020 202014 W Line 131 60000 0 WK13 2020 202013 W Line12N 12000 0 WK13 2020 202013 - amitchandak
Anonymous
Try something like this
averagex(summarize(Table,Table[Line No],Table[Workshop], "_avg", average(Table[ actual qty])),[_avg])
No, it's wrong, what I need is average of " sum(build QTY*total)/sum(Build QTY), I tried to add "*", but failed in average().