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.

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