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.
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 |
Super UserAnonymous
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().
- amitchandak
Anonymous ,
Try like
averagex(summarize(Table,Table[Line No],Table[Workshop],"_1",sumx(Table,Tablew[build QTY]*Table[total]), "_avg", sum(Table[Build QTY])),divide([_1],[_2])) Hi Anonymous not sure your MAX(week)...for cumulative? So I did not add this filter, you may modify it
VAR T1 = GROUPBY(NVL,NVL[Line No.],"SUMP",SUMX(CURRENTGROUP(),NVL[actual qty]*NVL[Total]),"TT",SUMX(CURRENTGROUP(),NVL[actual qty]))RETURNDIVIDE(MAXX(T1,[SUMP]),MAXX(T1,[TT]))Anonymous
系统显示， the syntax for "return" is incorrect.