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

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