Create a legend for multiple charts

[drattray]

Hello!

I have 5 bar charts, each of which is showing the top 5 items for the particular area that chart represents. As demonstrated below.

Now the thing is while there is overlap between the charts, not all the values have the same top 5.

I've looked at a couple different solutions, including this video from GuyinaCube.

This was generally what I was aiming for, however I've been unable to create a single bar chart to use as a legend for the other 5 charts which flexibly shows the all the distinct values in the above charts. What I mean here is the data is sliced by fiscal year and gender, so the top 5 is always shifting depending on the filter context, and thus the distinct top 5 across all the charts is anywhere from 5 to theoretically 25 different items.

The underlying data consists of a geography_table has columns for area identifers, a date_table that has month, calendar and fiscal year columns, a values_table that lists each individual instance of an item with its month date, item type, geography, and gender columns, and a item_dimension table that links the item type to a hexcolour code among other info.

Currently the bar charts are created by putting the item type column from the item_dimension table in the axis field, leaving legend blank so I can conditionally format on the item_dimension table hexcolour column, and values from an item_measure that counts the number of rows in the values_table for each item. The chart is filtered by a Top N of 5 for the item_measure and a filter for a area identifier from the geography_table.

This is where I'm currently at with creating the legend:

The issue here is I can only make it show the top 5 overall items, instead of the distinct values from all 5 top N charts.

Is there any suggestions of things to pursue that might solve my issue? I can try and mock up some sample data as well if that would be helpful.

Apologize for being a bit vague, but there is a necessary layer of privacy protection required,

and thank you for reading this far!

[Padycosmos]

I hope that the video in this link would solve your problem.

https://www.youtube.com/watch?v=-C2TwItozHw

[drattray]

Hi Padycosmos!

Thanks for the suggestion. I'm a bit hamstrung as my organization is using the December 2020 version...hoping to get an updated version soon.

However on watching the video I don't think it is going to achieve what I want to do.

Thanks for the suggestion though!