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
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
