Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Average by column

Hi Experts,

 

How to calculate the average per a certain column 

 

for example, as per the below table

end result should be (2+6+9)/3

 

ColumnA ColumnB
X                2
X                2

Y                6
Y                6
Z                9
Z                9

 

 

Thanks in advance

 

Need Help
Show and Tell
Tutorial Requests
  • VahidDM's avatar
    VahidDM
    5 years ago

    Anonymous 

    try this:

     

    Average distinct = 
AVERAGEX (DISTINCT('Table'),[ColumnB])

     

    Table : 

     

    Output:

     

    Click here to see more details about DISTINCT.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    Appreciate your Kudos  !!

     

7 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    5 years ago

    Anonymous 

    try this:

     

    Average distinct = 
AVERAGEX (DISTINCT('Table'),[ColumnB])

     

    Table : 

     

    Output:

     

    Click here to see more details about DISTINCT.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    Appreciate your Kudos  !!

     

  • VahidDM's avatar
    VahidDM
    Super User
    5 years ago

    Hi Anonymous 

     

    Try this measure:

    Average distinct =
AVERAGEX ( DISTINCT ( 'Table'[ColumnB] ), [ColumnB] )

    Output:

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    Appreciate your Kudos  !!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      this wont work if I have the below table

       as it will be (2+2+6+9)/4

      ColumnA ColumnB

      M               2
      X                2
      X                2

      Y                6
      Y                6
      Z                9
      Z                9

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        5 years ago

        Anonymous

        pls try this

        MEASURE = 
var tbl=SUMMARIZE('Table (2)','Table (2)'[ColumnA],"columnB",AVERAGE('Table (2)'[ColumnB]))
return AVERAGEX(tbl,[columnB])
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    You can create a measure as below to get it:

    Measure = 
VAR _tab =
    DISTINCT ( ALLNOBLANKROW ( 'Table'[ColumnA], 'Table'[ColumnB] ) )
RETURN
    AVERAGEX ( _tab, [ColumnB] )

    Best Regards