Create a pareto chart on any model within the SAC

[EHardwick]

Currently, SAC does not have a pareto chart option by default. However, with some calculated measures and dimensions it's possible to create one.

There are 5 total measure calculations we need, and 2 dimension calculations.

In this example our KPI of interest is QTY_Shipped and our dimension of interest is SKU, replace these with your own.

Calculations in order of use

1. Sum KPI: for this you need to do a SUM aggregation on the KPI you are interested in across the dimension you will split the chart by. E.g:

2. Restricted Sum KPI: now, create a restricted measure based on Sum KPI across the version dimension filtered to actual (as this will encompass all data, if this is a planning model then this wont work and you'll have to ask me to work it out 🙂 ), and with constant selection enabled across all dimensions. This, and step 1 are so we can get a grand total of the KPI of interest but across each of the members of the dimension of interest so we can get a % of the total:

3.Negative KPI: For our Dimension calculations, we first need to convert our KPI of interest to be negative, this is so later on the dimensions calculations are sorted correctly. We do this with a simple calculation: 

4.Negative Number Dimension: This is converting our negative KPI to a dimension. We are doing this so in a further calculation of a running total, we can calculate it across our numerical values so it sorts properly. For a pareto chart we sort from High to Low for our KPI, however, running total will calculate the total based on A-Z (or Low to High) hence why we need it to be negative. This calculation needs to be done over the dimension of interest and Version (which is auto added) and using the Negative KPI calculation. Make sure to check “Use measure values as dimension members”:

5. Numerical Dimension to Sort: This calculated dimension is used to convert our previous calculated dimension to a number. Whilst it looks like its a number, it actually is a string and is sorted as such. E.g 1,10,2,3,33,4 rather than 1,2,3,4,10,33. This Calculated dimension makes sure a dimension that has numerical values is treated as such:

6.Running Total: Now we use the “Running Total” calculation to create a Running total SUM of our KPI of interest over our dimension of interest (SKU) and our “Numerical Dimension to Sort”. Both dimensions are needed otherwise the running total will not calculate properly. As previously explained, this running total will calculate over the dimension selected, sorted by their IDs from A-Z. It does not respect the chart sort, this is why we have to create calculations 3,4, and 5 to get a dimension with IDs that when sorted would be the same sort as High-Low sorting of our KPI of interest over our dimension of interest.

7.Pareto %: This is our final calculation. It takes our running total and divides it by the Restricted Sum KPI to get that data points % of the grand total. After the calculation, we also need to edit its format to get it to display as a %. To do this, just untick “Use unit of underlying measure” and the change scale to “Percentage”:

Now, in your chart select combination column and line, and add the KPI of interest to the column axis, and the Pareto % to the line axis. Add you dimension of interest to the dimensions, and also add the “Numerical dimension to sort by”. Unfortunately, this is the only downside (apart from the number of calculations) as the calculations need this dimension to be in the chart.

Currently this only works on an unfiltered dataset. It might be possible to work on a filtered set so I will update the post with instructions when I can.

[hadar_k]

Hi, thanks for the simple step by step article. when created this in my system, the "Sort" option was graded out, as probably we have a lot of SKUs, so I ranked them by top 200. but then our graph in the end was not sorted by QTY and Pareto like shown. (top to bottom QTY and small to large in Pareto) any ideas?