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

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.