Forum Discussion

gvg's avatar
gvg
Icon for Post Prodigy rankPost Prodigy
8 years ago
Solved

Average calculation by criteria

Hi,   I would like to calculate average of Amount by some specific column in the same table. Thought this was simple, but I get strange results with this measure, which, I would expect, should do t...
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi gvg

     

    Try this MEASURE

     

    Calculated_Average =
IF (
    HASONEFILTER ( Table1[DocNo] ),
    CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) ),
    AVERAGEX (
        VALUES ( Table1[DocNo] ),
        CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) )
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi gvg

 

Try this MEASURE

 

Calculated_Average =
IF (
    HASONEFILTER ( Table1[DocNo] ),
    CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) ),
    AVERAGEX (
        VALUES ( Table1[DocNo] ),
        CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) )
    )
)
gvg's avatar
gvg
Icon for Post Prodigy rankPost Prodigy
8 years ago

Yep, this works like charm. Thank you !