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

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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