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:

 

 

EmployeeEmploymentAge 
1a35
1b35
2a40
3a35
4a51
4b51

 

I want to calculate the average age of the individual employees. All I know how to do is average the age column, but the obviously counts Employee 1 and 4's ages twice, giving me an average age of 41.2. 

 

I need a measure that counts distinct values in the employee column, then averages their ages. This should give me an average age of 40.3.

 

Any idea about a formula that could do this?

 

Thanks!

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

13 Replies

  • jahida's avatar
    jahida
    Impactful Individual
    10 years ago

    This should work (tested locally):

     

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

    • Turnipface's avatar
      Turnipface
      Advocate I
      8 years ago

      Hey Jahida,

      This looks like it can solve an issue that i'm experiencing.
      However, how could it work if the column "Employee" is in another table? 
      And would it be any different if it's not neccessarily a number, but text?

      • jahida's avatar
        jahida
        Impactful Individual
        8 years ago

        I'm not really sure what you mean... the original question wanted an average, what's an average of text values? Also if the columns are in different tables, how are they associated?

    • 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]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Awesome. Thanks a lot, it helped me.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Lydia,

       

      I am facing similar issue and your measure helped me a lot however In my case i need the average based on selection of three different slicers, i can see you have used one slicer with values (employees)in above measure. how about if I have three such values (three columns) that needs to be make averages of averages.