As part of a dashboard I’ve got a chart which is pivoted to show dates down the rows, and categories across the columns. Unfortunately, there are 20+ categories, and sorting them by volume the last 10+ are only showing a count of 1 or 2.
The resulting chart (stacked) looks really busy (even sorted descending) and the legend is especially busy.
What I’d like to do is group into a Top 5, and put anything else in “Other”. Preferably I’d like to show the Top 5 for each date period, so the legend might still be showing 10 categories + other.
I found something that used some sort of Row Order calculation, but I couldn’t get it to work, because I need the ordering over the columns not the rows, and there didn’t seem to be a Column equivalent. And if I switch the Date and Category in the pivot, I couldn’t get the stacked chart to still show dates on the bottom axis. Even if that had have worked, it wasn’t showing an “Other” anyway.
I’ve also come across references to using Derived Tables to get this to work, but honestly every time I try and look through the articles and work out how to apply it I get completely lost.
Is there a simple way to get a Top 5 and Other?
LinkedIn
Twitter
