The Pareto chart is a handy visual, but is not so easy to build in either excel or PowerBI. In a Pareto chart, information is provided about an individual product or category as a bar, and a cumulative scale as a line which compairs all bars. This type of visual can be extremely helpful when conducting failure mode analysis, causes of a problem, or even product portfolio balances. For some more information on Pareto charts you can learn more here or here. If you’re interested in building a Pareto chart in excel, I have found this post from Excel Easy to be helpful.
To give you a little teaser of what we will be building today, below you will see an image of the final Pareto chart. On the left side we have sales of units, and on the right is the cumulative percent of all sales. Using the Pareto chart a user has the ability to see which products comprise the majority of your sales. For example, the first 4 bars total approximately 50% of all sales.
Alright, let’s get started.
Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. Click Advanced Editor on the View ribbon. While in the Advanced Editor paste the following code into the editor window.
Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor. This tutorial teaches you how to copy and paste M code into the Advanced Editor.
let
Source = Excel.Workbook(Web.Contents("http://powerbi.tips/wp-content/uploads/2016/10/Sample-Data.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Item", type text}, {"Sales", Int64.Type}, {"Segment", type text}})
in
#"Changed Type"
Rename the Query to Data. Once you’ve completed the data load your data should look like the following.
On the Home ribbon click Close & Apply to complete the data load.
Let’s begin with a little exploration of our data.
Pro Tip: When I am building reports I often load the data and then immediately start building a couple of tables and slicers. It helps me understand how my data reacts to the slicers and helps me determine how to shape the data so that the visuals will work properly. For this example, we only have one table, but when loading data things can get rather complex due to loading multiple tables with multiple relationships.
Add a Slicer for the Segment. Enhance the look of the slicer by changing it from a vertical to a horizontal slicer. While the slicer is highlighted, click the Paint Roller expand the General section and change the orientation from vertical to Horizontal.
Repeat the same process to add a Slicer for the item field.
Next, add a table view of all the fields. Start with Segment, then Item and finally add Sales to the Table Visual.
Notice, now that we added all the Fields, there are a number of repeating values. We have Category 1 and Item 1 repeated 9 times. In some cases, it will be necessary to have this level of data brought into the data model within PowerBI. A common reason is that this level of granularity is required for other report pages, or visuals. It is OK to bring large amounts of data, but as a method of best practice it is recommended that you bring in the data required to support the visuals.
Now, to address these multiple items that we see in our data. In the sample Pareto image provided at the beginning of this Tutorial we only had one bar for Category 2 Item 3. Thus, we need to summarize each grouping of every Category and Item combination. To do this we will construct a summary table.
First, we will create a unique Key that will be used to summarize each combination of Category and Item pair. Click the bottom half of the New Measure button located on the Home ribbon.
Enter the following DAX expression. This new column titled Blend will be the unique Key that is utilized to summarize the data.
Blend = Data[Segment] & "-" & Data[Item]
Select the Modeling ribbon and then click on the New Table button. Enter the following DAX expression.
Summary = SUMMARIZE('Data', Data[Blend], "Sum Sales", SUM(Data[Sales]) )
For more information on the SUMMARIZE function you can visit the Mircosoft Summarize documentation page. In this equation we first select the table and in this case it is ‘Data’. Then the column we want to summarize or group by is the Segment column noted as Data[Blend]. The next field is the title of the summarized field column, noted as “Sum Sales”. Then DAX function that calculates the Sum of the column labeled Data[Sales], noted as SUM(Data[Sales]). It is relevant to point out here that the SUMMARIZE function will only work with building a new table and not as a calculated column or measure.
Add a new Table visual to the report and include the two newly created fields from the Summary table.
We have a field titled Blend which is our Key for all the summarized groupings. Next, we will want to parse out the Segments and Items from this blend column. We will want to use Category 1 & 2 in a slicer and the same for Items 1 to 5. Highlight the summary table by clicking the grey space next to the word Summary. Click the New Column button on the Modeling ribbon and enter the following DAX expression.
Segment = PATHITEM( SUBSTITUTE(Summary[Blend], "-" , "|" ), 1 )
In this expression the Substitute function replaced the dash “-” with a “|” character. Then the PATHITEM function can then parse the text into segments. By entering a 1 we select the first item in the sequence. For our example we only have two items, but when you’re working with file paths you can have multiple items in the path such as “\users\mike\my documents\my folder\”, which would equate to users = position 1, mike = position 2, my documents = position 3, etc..
Add another new column with the following DAX expression for the item column.
Item = PATHITEM(
SUBSTITUTE(Summary[Blend], "-" , "|" ),
2 )
Note: We changed the PATHITEM position from 1 to 2.
Next add the newly created Segment and Item columns to our summary table visual that we created earlier.
Nice job so far. Now we have to modify our slicers to point to the new Item and Segment fields we created in the Summary table. Select the Segment Slicer Visual and add the Segment Field from the Summary table.
Now that we have updated the slicers, we can now can control the table visual made from the Summary table.
Pro Tip: To select multiple items in a slicer you can hold down the Ctrl button on the key board and click multiple slicer items. This is how I was able to select Items 1 to 3.
Now we are ready to build the measures that will support the Pareto chart. Click on the bottom half of the New Measure button on the Home ribbon and select New Column. Add the following DAX expression to rank all the items in the Summary table.
Ranking = RANKX( 'Summary', 'Summary'[Sum Sales])
Add a measure for the Cumulative total according to the new ranking column we created. Click the top half of the New Measure button on the Home ribbon. Add the following DAX expression.
Cumulative Total = CALCULATE( SUM( Summary[Sum Sales] ), FILTER( ALLSELECTED( Summary ), Summary[Ranking] <= MAX( Summary[Ranking] ) ))
Repeat the add measure process and add a Total measure which will total only the items from the summary table that have been selected in the report view. Add the following DAX expression.
Total Sales = CALCULATE( SUM( Summary[Sum Sales] ) , ALLSELECTED( Summary ) )
For the last measure, repeat the process to add another measure. Enter the following DAX expression as a measure.
Cumulative Percent = [Cumulative Total] / [Total Sales]
The Cumulative Percent measure is a calculated as a percentage, thus we need to change this measure’s formatting to percentage. Click the measure labeled Cumulative Percent then change the Format to Percentage which is found on the Modeling ribbon.
Your Summary table should now look like the following.
To see all the calculations that we just created add all the fields from the Summary table to the Summary table visual we created earlier.
At last, we are ready to add the Pareto chart. Add the following fields to the line and stacked column chart.
Order the data in descending order by the number of sales by click the visual’s Ellipsis and selecting Sort By Sum Sales.
This changes the order of the items to make a Pareto chart.
Thanks for following along. Share if you enjoyed this tutorial.