Get average using Main Category column only but also include subcategories in the table.

[syddemo]

I have data of this data of thre column and I want output as Avg by Column. How to achive it?

The main problem is all three columns are from different tables but I have joined them.

Country	state	population	Avg by Country
India	punjab	2000	4750
India	haryana	4000	4750
India	Himachal	6000	4750
India	UP	7000	4750
canada	toronto	10000	15000
canada	Columbia	20000	15000

[dedelman_clng · Accepted solution]

Create a measure:

Avg by Country =
CALCULATE (
    AVERAGE ( 'Table'[population] ),
    ALLEXCEPT ( 'Table', 'Table'[Country] )
)

Hope this helps

David

[syddemo]

Hi @dedelman_clng. Thanks for your response. Will this work even if I am using all coumns from different tables (joins)?

[dedelman_clng]

As @az38 said, yes, if the model is correct.  Can you share a screen shot of your model showing the tables and their relationships?

[az38]

@syddemo 

it should work if you have correct relationships.

or what do you mean by JOIN exactly? SQL-JOIN or DAX-JOIN or what?