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] ) )
    )
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    gvg

     

    Actually your MEASURE is equivalent to

     

    CalculatedAverage = CALCULATE(AVERAGE(Table1[Amount]),ALL(Table1[DocNo]))

    The Calculate Expression

     

    CALCULATE (
    <expression>,
    table[column] = <value>
)

    is internally transformed into

     

    CALCULATE (
    <expression>, 
    FILTER (
        ALL ( table[column] ),
        table[column] = <value> 
    )
)
  • gvg's avatar
    gvg
    Icon for Post Prodigy rankPost Prodigy
    8 years ago

    Yep, this works like charm. Thank you !