Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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!!!