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.

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

      Anonymous 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       

      Anonymous  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