Forum Discussion
PowerBI calculate average (sumproduct) shows error data
Dear you, I'd like to calculate 2 columns data with sumproduct average, but result shows error, I don't know where's the problem, can you help me on this? Table: Line actual qty Total ...
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.
v-kelly-msft
Community Support
Community SupportHi Anonymous ，
I have added a row to enrich your data,as you see below:
Then I modify your measure to the following one:
Measure =
var a=CALCULATE(SUMX('Table','Table'[actual qty ]*'Table'[Total]),ALLEXCEPT('Table','Table'[Line]))
var b=CALCULATE(SUMX('Table','Table'[actual qty ]),ALLEXCEPT('Table','Table'[Line]))
Return
CALCULATE(DIVIDE(a,b),FILTER('Table','Table'[Week]<=MAXX(ALL('Table'),'Table'[Week])))
And you will see:
For the related .pbix file,pls click here.
Best Regards,
Kelly
Kelly
Did I answer your question? Mark my post as a solution!
Hi, Kelly, Thank you for your answer, you've provided one wonderful solution, but when I add more weeks of another year, it will be wrong, like this. Take Line12Q as example, it should be 18 (as raw data), but shows 11.47. (even I use weekNum, not week as calculation)
Workshop 202014 W 44.58 Line29 0.00 Line12Q 11.47 Line12N 4.18 Line30 40.92 Line131 0.00 Here is Raw Data
Workshop Line No. actual qty Total Week Year WeekNum W Line12Q 26208 15.8 WK52 2019 201952 W Line12Q 26208 15.8 WK51 2019 201951 W Line12Q 26208 15.8 WK50 2019 201950 W Line12Q 26208 15.8 WK49 2019 201949 W Line12Q 25725 18 WK14 2020 202014 W Line12Q 25725 18 WK13 2020 202013 W Line12Q 25725 18 WK12 2020 202012 W Line12Q 25725 18 WK11 2020 202011 W Line12Q 25725 18 WK10 2020 202010 W Line12Q 25725 18 WK09 2020 202009 W Line12Q 25725 18 WK08 2020 202008 W Line12Q 25725 18 WK03 2020 202003 W Line12Q 25725 18 WK01 2020 202001 W Line12Q 94044 3.7 WK52 2019 201952 W Line12Q 94044 3.7 WK51 2019 201951 W Line12Q 94044 3.7 WK50 2019 201950 W Line12Q 94044 3.7 WK49 2019 201949 W Line12Q 7700 18 WK14 2020 202014 W Line12Q 9000 18 WK14 2020 202014 W Line12Q 7700 18 WK13 2020 202013 W Line12Q 9000 18 WK13 2020 202013 W Line12Q 7700 18 WK12 2020 202012 W Line12Q 9000 18 WK12 2020 202012 W Line12Q 7700 18 WK11 2020 202011 W Line12Q 9000 18 WK11 2020 202011 W Line12Q 7700 18 WK10 2020 202010 W Line12Q 9000 18 WK10 2020 202010 W Line12Q 7700 18 WK09 2020 202009 W Line12Q 9000 18 WK09 2020 202009 W Line12Q 7700 18 WK08 2020 202008 W Line12Q 9000 18 WK08 2020 202008 W Line12Q 7700 18 WK03 2020 202003 W Line12Q 9000 18 WK03 2020 202003 W Line12Q 7700 18 WK01 2020 202001 W Line12Q 9000 18 WK01 2020 202001 W Line12Q 12762 11 WK52 2019 201952 W Line12Q 12762 11 WK51 2019 201951 W Line12Q 12762 11 WK50 2019 201950 W Line12Q 12762 11 WK49 2019 201949