Re: Dynamic change Axis AND Legend with Slicers

dirkbreeuwer · ‎05-17-2017

Hi guys, I am new to the community and excited to learn a lot! WIll be attending MS Data summit so let me know if you want to meet up.

I am trying to provide users of my reports with a powerful analytical experience and a self-service feeling.

The data I am working with looks like this (simplified):

I would like users to be able to use slicers to change both the axis AND the legend of the different graphs in the report.

So far I have managed to change EITHER the axis or the legend using the following technique:

Duplicate the table (shown above) then unpivoted it based on Year, Area, and Position columns.

The remaining table looks like this:

I link both tables using the index column. Then create a Stacked Column Chart, with year on the Axis, Value on the Legend, and Total Hires on The Value fields:

Is there any way I can create a new slicer that lets me change the Axis by the different attributes? Would be interesting to see all hiring by Area vs Position, or Area vs Year, etc.

Thanks in advance,

Dirk

mdannemiller · ‎06-02-2017

You could create a hierarchy of Area, Position, and Year. The first level would show Area totals, if you drill down without selecting a specific area, it will then show you position, and then by year.

Its not perfect but it does "change" the axis, and also gives you the ability to drill into an area and see position data for just that Area.

Anonymous · ‎08-07-2018

Hi @mdannemiller, Your solution some how helped me to dril down my chart while keeping only single chart and having multiple attribute at the same time. Now i want to show only top 5 value in each drill down state for each hirarchy i set. Is this possible in hirarchy drill down method? I am using donut chart. Please see my example screenshot attached here: