AverageX as column across related table

[SharonHMA]

I have a table with total asset values by customer. = 'Customer ID' column, 'Asset $' column.

I have another table that has 'Customer ID', 'Office Location'

No problem, in a visual I can get average Asset $ by location.

However, I want a KPI measure that shows say Barrie (a location) average Asset $ as a % of all location averages. (I'll be doing this for several other tables so we can compare several averages across multiple locations for drivers).  And I want this to 'stick' even if we filter by territories that would include Barrie or by Barrie location.

Effectively Average Asset $ by Location / Average Asset $ all locations

I've tried to create a column but I can't get location because it's in another reference table?

I have 8 different query tables / data points to do this, and I'd like to do this for territory & location so merging the data into a merged query seems like a crazy way to go but the only other way I can think of.

Any help appreciated.

[v-cherch-msft]

Hi @SharonHMA 

Could you share some simple sample data which could reproduce your scenario and your desired output so that we could help further on it?Please follow this article How to Get Your Question Answered Quickly to post your data and expected output.

Regards,