Average of Category (across tables)

[SiroPW]

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.

[AllisonKennedy]

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])

[SiroPW]

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]

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

[harshnathani · Accepted solution]

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)

[AllisonKennedy]

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.