Average calculation by criteria

[gvg]

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 the job:

Calculated Average = CALCULATE(AVERAGE(Table1[Amount]),Table1[DocNo])

This is my source table and the result. Why the measure refuses to calculate average on DocNo, and instead arbitrarily calculates average on Manager?

I am expecting Calculated Average to be like this regardless of Manager field:

Manager   DocNo       Calculated Average

John          322            35

John          566            20

Peter         123            10

Sam           322            35

-----------------------------

Total:                           21.7      

21.7 being  (35 + 20 + 10) / 3. Standard Quick Measure comes up with 33 in this case.

[Zubair_Muhammad · Accepted solution]

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]

Yep, this works like charm. Thank you !

[Zubair_Muhammad]

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