cancel
Showing results for
Did you mean:
Helper I

## Average of Category (across tables)

Hi All,

I feel like this is something that should be simple but I can't find the magic bullet after trying a lot.

I have 2 linked tables (Many to 1):

Table A:

 name age id abc 20 1 abc 30 1 xyz 50 2 xyz 60 2 def 70 3

Table B:

 value id 100 1 200 2 300 3

I'd like to be able to output the value of each id by their average age.

So id 1, the average age from table A would be 25.

Output would be like so:

 avg_age value 25 100 55 200 70 300

I have tried a lot of combinations with CALCULATE, AVERAGEX, ALLEXCEPT, FILTER and still can't get it, hope I have outlined the problem well enough.

Thanks for any help.

1 ACCEPTED SOLUTION
Super User III

Hi @SiroPW ,

You can just create a Calculated Column.

Average Age = CALCULATE(Average('Table'[age]),ALLEXCEPT('Table','Table'[id]))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

5 REPLIES 5
Super User III

What additional filters or slicers do you need to work with this? You should just be able to do a standard average, just need to use the correct columns in the visual. Create a new MEASURE:

Average Age = AVERAGE(TableA[Age])

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

If you found this post helpful, please give Kudos. ?

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

www.excelwithallison.com

Helper I

I think I have explained this poorly.

Let me simplify..

Given this Table A:

Table A:

 name age id abc 20 1 abc 30 1 xyz 50 2 xyz 60 2 def 70 3

How do I add a new column (average age based on the id) such that Table A is now:

Table A:

 name age id avg_age abc 20 1 25 abc 30 1 25 xyz 50 2 55 xyz 60 2 55 def 70 3 70
Anonymous
Not applicable
Why do it in DAX? Isn't it sooooo much easier (and better!) to do it in Power Query? If you do it in PQ and the table is big, it'll not only be faster but you'll get optimal compression, which is not possible through DAX.

Be careful with code as given by harshnathani. If the table is big (think: fact table), CALCULATE will execute context transition for each and every row which will be extremely costly and can easily lead to out-of-memory errors.

Best
D
Super User III

Hi @SiroPW ,

You can just create a Calculated Column.

Average Age = CALCULATE(Average('Table'[age]),ALLEXCEPT('Table','Table'[id]))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Super User III

Why do you need it as a column? If you create the MEASURE, it can be used in a table visualization. Can you please elaborate on your requirements so we can help further? I generally don't recommend doing aggregations (such as average) in a calculated column as this can be used incorrectly in visuals and cause incorrect information later.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

If you found this post helpful, please give Kudos. ?

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

www.excelwithallison.com

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors