Forum

Notifications
Clear all

PivotCharts Slicer Filter - legend disappears and secondary axis column format

2 Posts
2 Users
0 Reactions
229 Views
(@markelliott)
Posts: 2
Active Member
Topic starter
 

Hello,

Apologies if this is dumb novice question.

Attached excel file has a table of Australian census data for Local Government Areas in Victoria.

I have 3 pivot tables and pivot charts (pivot charts are copied into a dashboard tab and added a slicer for the LGA names). Goal is to create an easy to view summary of census data for each LGA.

After selecting the Alpine LGA name in the slicer - the population numbers for total population and CALD population (on the secondary axis) columns look identical. Why is this when the population numbers are different?

In the % and Rank charts the values recalculate to give a % of 100% and a Rank of 1 (I think I need to use a DAX measure to fix this but have no idea where to start.

Thanks,

Mark

 
Posted : 26/02/2023 11:41 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Mark,

When you plot data on a secondary axis it scales the axis to suit the values it's plotting while making use of the available space, as a result, the two columns for the max values on each axis will be about the same height. I would avoid having the total and then subsets of the total value in the same chart. You'd be better to show it as a stacked column chart that shows the 3 groups in the stack:

  • CALD People
  • Aboriginal People
  • The rest which makes up the total

As for your percentage calculation, yes you will need to load the data to the data model and write a DAX measure that ignores filters. Looking at your query, you could avoid merging all the data into a single table and instead make use of Power Pivot's ability to create relationships between the tables. There is a tutorial on RANKX here.

Mynda

 
Posted : 27/02/2023 8:00 pm
Share: