cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SiroPW
Helper I
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:

nameageid
abc201
abc301
xyz502
xyz602
def703

 

Table B:

valueid
1001
2002
3003

 

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_agevalue
25100
55200
70300

 

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

Hi @SiroPW ,

 

 

You can just create a Calculated Column.

 

 

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

Regards,
Harsh Nathani

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

View solution in original post

5 REPLIES 5
AllisonKennedy
Super User III
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

I think I have explained this poorly.

 

Let me simplify..

 

Given this Table A:

Table A:

nameageid
abc201
abc301
xyz502
xyz602
def703

 

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

Table A:

nameageidavg_age
abc20125
abc30125
xyz50255
xyz60255
def70370
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

Hi @SiroPW ,

 

 

You can just create a Calculated Column.

 

 

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

Regards,
Harsh Nathani

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

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

Get Ready for Power BI Dev Camp

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