Looking for an automated way to Pareto Dashboard data in a Chart Widget from a Report
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.