cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bullius
Helper V
Helper V

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!

2 ACCEPTED SOLUTIONS
jahida
Impactful Individual
Impactful Individual

This should work (tested locally):

 

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

View solution in original post

v-yuezhe-msft
Microsoft
Microsoft

Hi @bullius,

You can also create the measure using the formula.

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



Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-yuezhe-msft
Microsoft
Microsoft

Hi @bullius,

You can also create the measure using the formula.

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



Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thanks for the solutions! 

jahida
Impactful Individual
Impactful Individual

This should work (tested locally):

 

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

View solution in original post

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.

Try adding in a FILTER clause, e.g.

 

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

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
Impactful Individual
Impactful Individual

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?

Right....it's a bit more complicated than i thought 🙂

Let's say i have 3 tables.
Table 1 - TransactionID - gives me the information about the transaction. Date, time, etc. ID is always unique. Details not included in the example - only ID

 

Transaction ID

1
2
3
4
5

Table 2 - Transaction - Details of what items the transaction included, always referencing the ID, but extending over multiple rows, depending on the size of the transaction.

 

Transaction ID     Item

1Banana
1Turnip
1Chair
1Banana
1Banana
1Apple
2Turnip
2Cabbage
2Apple
3Banana
3Cucumber
3Turnip
4Banana
5Chair

 

Table 3 - Item ID - Extended information about the items that summarizes them in categories.

 

Item ID      Item                 Category

11BananaFruit
12TurnipVegetable
13ChairFurniture
14AppleFruit
15CabbageVegetable
16CucumberVegetable

 

From the below screenshot, what i care about is the Distinct count for the Item Categories, meaning that i don't need to know how many of them are in total, but how many Transactions include which of the Categories (thus the DistinctCount, using Measure = DISTINCTCOUNT)

image.png

 

 What i'm looking to find is the Average of each category Per Transaction. I want to be able to track the categories with the largest average per transaction (as well as the one with the lowest, and see trends, etc.)


So if i'm correct, that'd mean that the Average for Furniture should be 0.4 (2/5), Vegetables would be 0.6 (3/5) and Fruits would be 0.8 (4/5).
I have the feeling that this is very simple and it's a little bit that i'm missing, but i just can't get the right formulas to work for this.

The .pbix mock file can be found here, if needed.


Any help/tips will be appreciated! 

Thanks!

jahida
Impactful Individual
Impactful Individual

First of all, that was a really good clarification post, thank you for making it easy to understand your situation.

 

I think the measure that you had (DISTINCTCOUNT) was close, what you wanted was just some way to divide by total transactions. There's probably a few ways to do this, I'm a bit rusty so the first one that came to my mind was using CALCULATE. Here's the measure I used:

 

Measure 2 = DISTINCTCOUNT('Transaction'[Transaction ID])/CALCULATE(DISTINCTCOUNT('Transaction'[Transaction ID]), ALL('ItemID'[Category]))

 

Note that the first bit is the same as your measure, and then you divide it by the count of transactions across all categories. It gives the results you said were the expected (you might need to delete and remake your measure so it reformats). I hope that helps!

Amazing! This is exactly what i was looking for! 
Thanks for your help, especially considering this is already a solved topic and you won't get the credit 😉

Have a lovely weekend ahead and holidays if you're celebrating!


jahida
Impactful Individual
Impactful Individual

Haha no problem, glad to help. Like I said, you made it easy. Happy Holidays to you too!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors