Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:
Lineactual qty TotalWeekCol_NVL
Line012572518WK1411.4703936289298
Line01770018WK1411.4703936289298
Line01900018WK1411.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)

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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.

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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.

WorkshopLine No.actual qtyTotalWeekYearWeekNum
WLine12Q2620815.8WK522019201952
WLine12Q2620815.8WK512019201951
WLine12Q2620815.8WK502019201950
WLine12Q2620815.8WK492019201949
WLine12Q2572518WK142020202014
WLine12Q2572518WK132020202013
WLine12Q2572518WK122020202012
WLine12Q2572518WK112020202011
WLine12Q2572518WK102020202010
WLine12Q2572518WK092020202009
WLine12Q2572518WK082020202008
WLine12Q2572518WK032020202003
WLine12Q2572518WK012020202001
WLine12Q940443.7WK522019201952
WLine12Q940443.7WK512019201951
WLine12Q940443.7WK502019201950
WLine12Q940443.7WK492019201949
WLine12Q770018WK142020202014
WLine12Q900018WK142020202014
WLine12Q770018WK132020202013
WLine12Q900018WK132020202013
WLine12Q770018WK122020202012
WLine12Q900018WK122020202012
WLine12Q770018WK112020202011
WLine12Q900018WK112020202011
WLine12Q770018WK102020202010
WLine12Q900018WK102020202010
WLine12Q770018WK092020202009
WLine12Q900018WK092020202009
WLine12Q770018WK082020202008
WLine12Q900018WK082020202008
WLine12Q770018WK032020202003
WLine12Q900018WK032020202003
WLine12Q770018WK012020202001
WLine12Q900018WK012020202001
WLine12Q1276211WK522019201952
WLine12Q1276211WK512019201951
WLine12Q1276211WK502019201950
WLine12Q1276211WK492019201949
Anonymous
Not applicable

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
Community Support
Community Support

Hi @Anonymous ,

 

I have added a row to enrich your data,as you see below:

Annotation 2020-04-10 171316.png

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:

Annotation 2020-04-10 171605.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

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

 

Workshop202014
          W44.58
Line290.00
Line12Q11.47
Line12N4.18
Line3040.92
Line1310.00

 

Here is Raw Data

WorkshopLine No.actual qtyTotalWeekYearWeekNum
WLine12Q2620815.8WK522019201952
WLine12Q2620815.8WK512019201951
WLine12Q2620815.8WK502019201950
WLine12Q2620815.8WK492019201949
WLine12Q2572518WK142020202014
WLine12Q2572518WK132020202013
WLine12Q2572518WK122020202012
WLine12Q2572518WK112020202011
WLine12Q2572518WK102020202010
WLine12Q2572518WK092020202009
WLine12Q2572518WK082020202008
WLine12Q2572518WK032020202003
WLine12Q2572518WK012020202001
WLine12Q940443.7WK522019201952
WLine12Q940443.7WK512019201951
WLine12Q940443.7WK502019201950
WLine12Q940443.7WK492019201949
WLine12Q770018WK142020202014
WLine12Q900018WK142020202014
WLine12Q770018WK132020202013
WLine12Q900018WK132020202013
WLine12Q770018WK122020202012
WLine12Q900018WK122020202012
WLine12Q770018WK112020202011
WLine12Q900018WK112020202011
WLine12Q770018WK102020202010
WLine12Q900018WK102020202010
WLine12Q770018WK092020202009
WLine12Q900018WK092020202009
WLine12Q770018WK082020202008
WLine12Q900018WK082020202008
WLine12Q770018WK032020202003
WLine12Q900018WK032020202003
WLine12Q770018WK012020202001
WLine12Q900018WK012020202001
WLine12Q1276211WK522019201952
WLine12Q1276211WK512019201951
WLine12Q1276211WK502019201950
WLine12Q1276211WK492019201949

 

amitchandak
Super User
Super User

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
Not applicable

Thank you, 

 

Workshop202014
          W5.22
Line290.00
Line12Q18.00
Line12N0.00
Line3032.80
Line1310.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.

WorkshopLine No.actual qtyTotalWeekYearWeekNum
WLine 1311287210WK522019201952
WLine 1311287210WK512019201951
WLine 1311287210WK502019201950
WLine 1311287210WK492019201949
WLine12N120000WK142020202014
WLine 131600000WK132020202013
WLine12N120000WK132020202013

@Anonymous 

Try something like this

averagex(summarize(Table,Table[Line No],Table[Workshop], "_avg", average(Table[ actual qty])),[_avg])

Anonymous
Not applicable

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

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
Not applicable

 

@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
Not applicable

@Vera_33 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.