I created a clustered column chart that links to a Shape Map of communities in a city and displays the number of residents based on age groups. When I click on a community in the Shape Map, the columns in the chart change to reflect the values for that community. All is well.
What I wanted to do is display a second column of the citywide average for each age group. I created this measure:
Average of Age Group Per Community Name =
KEEPFILTERS(VALUES('Census Ages'[Community Name])),
CALCULATE(AVERAGE('Census Ages'[Count]), FILTER('Census Ages', 'Census Ages'[Count] <> 0))
and put the measure and [Count] into 'value' and [Age Groups] into 'Axis'. However when I click on a community in the Shape Map, it recalculates the average based on that slicer. What I want is for the column showing the city average to remain unchanged so that it can be compared based on where you click in the Shape Map.
I hope that makes sense. Thanks for your help.
I'm a still little confused about your scenario.
If it is convenient, could you share some data sample and your desired output so that I can understand your scenario better and help further on it.
For example, if I had 3 communitties and 3 age groups. So I have a clustered column chart connected to a shape map of the city with Age Group on the axis and Count in Value. When no neighbourhood is selected it displays the total for the city, when a neighbourhood in the shape map is selected, the chart changes to just represent that neighbourhood. What I want is two bars, one reflecting the average amounts in each age group and one showing the count of the selected neighbourhood. But when a neighbourhood is selected, I don't want the average bar to change.
I hope that makes more sense.