Hi - I've created a Pareto table that uses running sum % (of sales) to split my category information (Title) into 80/20 buckets. It works fine except for one thing: I'm not able to aggregate the sales data into just two lines (the Top 80% and All Other buckets). No matter what I do the sales data stays disaggregated at the Title level. Any suggestions?
@Joel Acker well you've a great job on the pareto but the issue is the use of table calc's, which provide a displayed value, not a real value to work with going forward. And, in this case due to use of running sum requires the detail to be in the view as rows
. It becomes extremely difficult to aggregate thereafter as a 'total'. Even if you were able to change the Pareto calc to be a measure, something along the lines of:
80% Pareto BucketWINDOW_SUM(IF [Running Sum %] <= FLOAT([Top N %])
THEN SUM([Sales])
END)
That would break out the 80 vs 20, but it would still require the title to be in the detail. Of which, any changes to Title would be cause for the above calc to break. Closest you'd get with this in Tableau would be something like:
...and Grand Totals won't work here. These are limitations of Table Calc's that involve use and rely upon other table calc's such as Running_Sum(). That said, I'd suggest a workaround of using Tableau Prep to 'assign' whether a value falls into the appropriate bucket so that in turn you could easily aggregate the results.
For example in Tableau Prep, a running sum can be handled at the row level:
Add in the other calc's to determine 80 / 20 sorted on Data Source order:
The Output of that is then used in Desktop, with a slight variance in the sales by 80/20 but the ratio's being virtually same:
That would be a better approach. Attaching the packaged flow as example.
