Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.

Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.