Forum Discussion

gvg's avatar
gvg
Post 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 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.

 

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

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community 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
      Community 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
      Post Prodigy
      8 years ago

      Yep, this works like charm. Thank you !