A Pareto analysis helps us to identify which groups of customers contribute to the first 80% of our sales. To create a Pareto analysis, we will use a combo chart.
The combo chart allows us to combine metrics with different shapes as bars, lines, and symbols. We will represent the data in two axes; the primary axis is found at the left-hand side of the chart, and the secondary axis is found at the right-hand side of the chart.
In our example, the chart has a bar for Sales $ in the primary axis, as well as two lines: one for the Cumulative % of sales, and the other as static, with 80% in the secondary axis.
In the following screenshot, you can see the highlighted customers contributing to the first 80% of the sales. These are our best customers:
To create the Pareto analysis chart, follow these steps:
- Click on the Charts button on the asset panel, which is on the left-hand side of the screen, and find the Combo chart.
- Click on the Combo chart and drag and drop it into the empty space at the right-hand side of the sheet.
- Click on Add Dimension and select Customer in the Dimension section.
- Click on Add Measure and select Sales $ in the Measures section.
- The combo chart will look like this:
- We need to add two other measures, represented by lines. The first is the cumulative percentage of sales, and the second is the reference line at 80%. To add the cumulative sales line, go to the properties panel, expand the Data section, and click on the Add button in Measures:
- Click on the fx button to open the expression editor:
- Type the following expression in the Expression editor:
RangeSum(Above(Sum(SalesAmount), 0, RowNo())) / Sum(total SalesAmount)
This expression will calculate a cumulative ratio of the sales for each customer, over the whole amount of the sales of all customers.
We can break this expression down into two parts to understand how it works:
-
- RangeSum(Above(Sum(SalesAmount), 0, RowNo())): In the first part of the expression, the Rangesum() function will summarize the numbers in an array of values returned by the Above() function. The array contains all of the preceding values in the chart, from row 0 (the second parameter) to the current row returned by the RowNo() function.
- / Sum(total SalesAmount): In the second part of the expression, the cumulative sum of the sales will be divided by the sum of the sales for all customers. We have to use the total qualifier inside of the sum() function to disregard the dimensions of the chart and to return the sales for all customers.
- Click on the Apply button to close the expression editor and save the expression.
- Set the Label of the new measure to Cumulative Sales %.
- Check if the properties Line is selected and that Secondary axis is selected for the measure. The properties panel will then look like what's shown in the following screenshot:
- Change the number formatting to Number, set the formatting option to Simple, and select 12.3%:
- Now, find the Add button in the Measure pane to add another measure: the reference line for 80%.
- Open the Expression editor, type 0.8, and click on the Apply button.
- Set the Label to 80%.
- Check if the properties Line is selected and that the Secondary axis is selected for the measure. The properties panel will look like what's shown in the following screenshot:
The combo chart is almost complete. We also need to fix the sort order into a descending fashion, by Sales $:
- Go to the properties panel and expand the Sorting section.
- Click on Customer to expand the Sorting configuration for the dimension.
- Switch off the Auto sorting.
- Click on the checkbox for Sort by expression to select the option.
- Open the Expression editor and type the following:
- Click on Apply to close the expression editor and apply the changes.
- Set the Title of the chart to Pareto Analysis.
- Change the sorting order to Descending.
- Deselect other sorting options if they are selected. The Sorting pane will look like this:
- Finally, the combo chart will look like what's shown in the following screenshot:
