cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SeanKH19 Frequent Visitor
Frequent Visitor

Clustered Column Where One Column Doesn't Change

Hi,

 

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 = 
AVERAGEX(
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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Clustered Column Where One Column Doesn't Change

Hi @SeanKH19,

 

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.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SeanKH19 Frequent Visitor
Frequent Visitor

Re: Clustered Column Where One Column Doesn't Change

Book-_2_.jpg

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.