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

 

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.

12 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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, 

       

      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
      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        Anonymous 

        Try something like this

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

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

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

      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

       

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