Looking for an automated way to Pareto Dashboard data in a Chart Widget from a Report

Jake Gustafson

I have large datasets with various records that have spikes and some that have only a few occurrences. Right now I have things structured in a main sheet where all the data is entered, then a summary sheet with the potential values (which need to be manually maintained to some degree) with a VLOOKUP formula to count the instances. Then I have a graph widget on my Dashboard to display the top ~80% of records, so that the widget isn't bogged down with a bunch of tiny values, but if someone is interested, then they can click on the widget and drill into the full dataset. What I'd like to be able to do is tie that chart widget to a Report that would dynamically update based on what the users enter in the respective column. Then set the widget to only display individual pie slices or bars for the top 80% or something similar. To me, that seems like a more automated approach, where no one would need to manage the data and manually adjust things as new values are entered or a big shift in the population of one value.

Not sure if this is something that is already out there as a product enhancement or if others have struggled with it also. When dealing with creating sheets/reports/dashboards to other users, they aren't going to want to maintain that data analytics, but they certainly will like to see the info. So the less touch points needed to keep things up, the better.

Genevieve P.

Hi @Jake Gustafson

Depending on how you have your Summary Sheet set up, it sounds like you could add an extra column that will identify the Rank for each value. (See: RANKEQ Function)

Then once you have a Rank per-row, you can use a Report to filter by this Rank column and only show the top 10 or top 20, etc. The formula will automatically update the Ranks as your values shift up and down, and the Report will automatically only bring in the rows from your Summary Sheet that are in the rank you identified. Then your Chart will be based on the Report. Would that work for you?

If you need help building out the Rank formula, it would be useful to see a screen capture of that Summary Sheet where you have the cross-sheet formulas.

Cheers!

Genevieve

Genevieve P.

Hi @Jake Gustafson

Depending on how you have your Summary Sheet set up, it sounds like you could add an extra column that will identify the Rank for each value. (See: RANKEQ Function)

Then once you have a Rank per-row, you can use a Report to filter by this Rank column and only show the top 10 or top 20, etc. The formula will automatically update the Ranks as your values shift up and down, and the Report will automatically only bring in the rows from your Summary Sheet that are in the rank you identified. Then your Chart will be based on the Report. Would that work for you?

If you need help building out the Rank formula, it would be useful to see a screen capture of that Summary Sheet where you have the cross-sheet formulas.

Cheers!

Genevieve

Jake Gustafson

Thanks @Genevieve P. I was able to incorporate the RANKEQ Function into my Summary Sheets and create a 'better' Report based on those rankings. A nice new tool in my arsenal.