PowerBI calculate average (sumproduct) shows error data

[Anonymous]

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	Week	Col_NVL
Line01	25725	18	WK14	11.4703936289298
Line01	7700	18	WK14	11.4703936289298
Line01	9000	18	WK14	11.4703936289298

Fomular:

 Col_NVL = CALCULATE( averagex( 'NVL',sumx('NVL','NVL'[actual qty]*'NVL'[Total])/sum(NVL[actual  qty])),FILTER(ALLEXCEPT('NVL',NVL[Line.]),'NVL'[Week]<=max('NVL'[Week])))

it should be:  (25725*18+7700*18+9000*18)/(25725+7700+9000) = 18   (note: total sometimes same, sometimes not)

[Anonymous]

Thank you all for the suggestions, till now, the most close formula is still:

ave= CALCULATE(

divide(sumx('NVL','NVL'[actual qty]*'NVL'[Total]),sum(NVL[actual  qty])),ALLEXCEPT('NVL',NVL[Workshop]),FILTER('NVL','NVL'[Week]<='NVL'[Week])), but still, when summarize as Line No. is right, when go to the upper level, workshop level, average is larger than it should be.

Since data is big, don't know how to upload, so here just take one Line data as example.

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

[Anonymous]

I found the root cause here:

Average for sub-level result is not equal to average for whole raw data. For example:

   Group    Sub-level   Data

   A            A1                 2       

   A            A1                 2.2

  A             A2                 2.1

  B             B1                 2.7

Average A = average (A1,A2,B1)    Does not the same with

Average A = average (data)          

Thank you all!!!

[v-kelly-msft]

Hi @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

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft 

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

[amitchandak · Accepted solution]

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.

[Anonymous]

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])

[Anonymous]

@amitchandak 

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().

[Vera_33]

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]))

RETURN

DIVIDE(MAXX(T1,[SUMP]),MAXX(T1,[TT]))

[Anonymous]

@Vera_33  result shows, all lines output are the same, actually not. would you pls have a look the data which I sent to @v-kelly-msft , I don't want to accumulate it, but if I remove <=max(), then all line data are the same

Workshop Line.          Week     02bbbb
W               Line 131    WK14      230
W              Line 30      WK14        230
W              Line12N    WK14      230
W              Line12Q    WK14      230
W              Line29      WK14         230

[Anonymous]

@Vera_33 

系统显示， the syntax for "return" is incorrect. 

[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]))