Forum Discussion

bullius's avatar
bullius
Helper V
10 years ago
Solved

Average based on distinct values in another column

Hello,   I have data that looks like this:     Employee Employment Age  1 a 35 1 b 35 2 a 40 3 a 35 4 a 51 4 b 51   I want to calculate the average age of...
Need Help
  • jahida's avatar
    jahida
    10 years ago

    This should work (tested locally):

     

    Measure = AVERAGEX(SUMMARIZE(Table1, Table1[Employee], Table1[Age]), Table1[Age])

  • Anonymous's avatar
    Anonymous
    10 years ago

    Hi bullius,

    You can also create the measure using the formula.

    Measure  = AVERAGEX(VALUES(Table[Employee]), CALCULATE(AVERAGE(Table[Age])))



    Thanks,
    Lydia Zhang

huguest's avatar
huguest
Advocate II
8 years ago

Hello, older thread but taking a chance here...  I have a very similar scenario, except that I need to include a additional parameter to the equation.  To continue with the OP's example, I would also have another column for the employee gender, and need a formula that will calculate the average age of Male employees.  How can this be done?

Thanks.

bullius's avatar
bullius
Helper V
8 years ago

Try adding in a FILTER clause, e.g.

 

AVERAGEX (
    SUMMARIZE (
        FILTER (
            Table1,
            Table1[Gender] = "Male"
        ),
        Table1[Employee], 
        Table1[Age]
    ), 
    Table1[Age]
)