Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi @SiroPW ,
You can just create a Calculated Column.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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])
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, 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:
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 |
Hi @SiroPW ,
You can just create a Calculated Column.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |