Unleashing the Power of Pareto Analysis in Power BI Using DAX Measures
Understanding and Implementing the Pareto Principle with Power BI
Hello friends, Pradeep here from Tech and Training Corporate Solutions. Today, we’ll discuss the Pareto Principle, also known as the 80/20 rule. This principle states that 20% of causes are responsible for 80% of effects. For instance, 20% of the global population holds 80% of the wealth. Similarly, in businesses, it’s often found that 20% of products generate 80% of the company’s revenue. This principle can be applied to a plethora of scenarios in our everyday lives.
In Excel, creating a Pareto chart to visualize this principle is straightforward. However, in Power BI, it’s a bit more complex. So, let’s dive into how to create a Pareto chart in Power BI.
To start with, you’ll see a table on the left-hand side showing product details like the product names, their total sales, and the cumulative percentage. On the right-hand side, you’ll find a Pareto chart. The green columns represent total sales, the red line shows the cumulative percentage, and the horizontal line at 80% indicates that the products to the left of this line are the 20% that generate 80% of the company’s revenue.
To create a Pareto chart in Power BI, we first need to have the product details table connected to the order details table in a one-to-many relationship. The products table contains unique product details, which is connected to the order details table. The order details table contains a column for total sales, which we’ll use to calculate the Pareto Principle.
First, we’ll create a measure in Power BI for total sales using a simple DAX function, SUM. Next, we’ll create a table of product names and total sales. Sorting this table in descending order gives us the products with respect to their total sales.
Total Sales Measure: This measure calculates the total sales amount for each product. The SUM function is a simple aggregation function that adds up the ‘Total Sales’ for each product.
Total Sales = SUM(‘Order Details'[Total Sales])
The real challenge is creating the cumulative total. To do this, we’ll create another measure using the RANKX DAX function to rank each of the products based on their total sales
Rank Measure: This measure assigns a rank to each product based on its total sales. The RANKX function assigns a rank number to each row in the ‘Products’ table, based on the ‘Total Sales’ value, sorted in descending order. HASONEVALUE function returns true when the column has exactly one value in the context. BLANK function returns a blank. So, if the ‘Product Name’ column doesn’t have a unique value, it returns blank.
Rank =
IF(
HASONEVALUE(‘Products'[Product Name]),
RANKX(ALL(‘Products’), [Total Sales], , DESC),
BLANK()
)
The next step is to create the cumulative total. We’ll use the SUMX and TOPN DAX functions to do this. TOPN will return a table of the top N rows based on total sales, and SUMX will add up the total sales at each row, thereby creating a cumulative total.
Cumulative Total Measure: This measure calculates the cumulative total sales for each product, sorted in descending order by total sales. The TOPN function returns the top ‘n’ rows of the specified table. In this case, ‘n’ is the rank of the current product. SUMX adds up the ‘Total Sales’ for these top ‘n’ products. So, for each product, it sums up the total sales of that product and all products with a higher rank (higher total sales).
Cumulative Total =
IF(
HASONEVALUE(‘Products'[Product Name]),
SUMX(
TOPN(
[Rank],
ALL(‘Products’),
[Total Sales],
DESC
),
[Total Sales]
),
BLANK()
)
After creating the cumulative total, we need to calculate the cumulative percentage. This involves dividing the cumulative total by the total sales across all rows. We’ll use the DIVIDE DAX function to do this.
Total Sales of All Products Measure: This measure calculates the total sales for all products in the dataset. The ALL function removes all filters from the ‘Product Name’ column, and then SUMX adds up the ‘Total Sales’ for all products.
Total Sales of All Products = SUMX(ALL(‘Products'[Product Name]), [Total Sales])
Cumulative Percentage Measure: This measure calculates the cumulative percentage of total sales for each product. The DIVIDE function divides the ‘Cumulative Total’ by the ‘Total Sales of All Products’. This gives the proportion of total sales represented by the top ‘n’ products, which is the cumulative percentage of total sales.
Cumulative Percentage =
DIVIDE(
[Cumulative Total],
[Total Sales of All Products],
0
)
Once we have the total sales and cumulative percentage, we can create the Pareto chart. We don’t need the cumulative total, rank, or total sales of all products for this chart – just the total sales and cumulative percentage.
- Add a Visual: From the Home ribbon, click on the ‘Combo Chart’ button. This will add a new combo chart visual to your report. A combo chart is a combination of a bar chart and a line chart, which is perfect for a Pareto chart.
- Add Data Fields to the Visual: In the ‘Fields’ pane, expand the ‘Products’ table, and drag the ‘Product Name’ field to the ‘Axis’ well of the visual. Next, drag the ‘Total Sales’ measure to the ‘Column values’ well, and the ‘Cumulative Percentage’ measure to the ‘Line values’ well.
- Sort the Data: To ensure the chart is sorted by the total sales in descending order, in the ‘Visualizations’ pane, click on the ‘…’ in the top-right corner of the ‘Fields’ pane and select ‘Sort ascending’ and then ‘Sort by column -> Total Sales’.
- Format the Line Chart: To format the line chart to display as a percentage, in the ‘Visualizations’ pane, click on the ‘Format’ button, then expand the ‘Y2 axis’ section. Change the ‘Data type’ to ‘Percentage’, and adjust the ‘Start’ and ‘End’ values as necessary (you might want to set ‘End’ to 1, to represent 100%).
- Adjust the Chart Title, Labels, and Legend: You can also adjust the chart title, labels, and legend to make your chart more readable. For example, you might want to give your chart a title like “Pareto Chart of Product Sales”, label the y-axis as “Total Sales” and “Cumulative Percentage”, and label the x-axis as “Products”.
- Highlight the 80% Line: To highlight the 80% line on the Pareto chart, go to ‘Analytics’ tab in the ‘Visualizations’ pane, click on the ‘+ Add’ button under ‘Constant Line’. In the ‘Value’ field, enter ‘0.8’ and format the line as you wish. This line will give a visual representation of where the 80% of total sales lies on the chart.
- Interact with the Chart: Now, you can interact with the chart. Hovering over the bars will show you the total sales for each product, and hovering over the line will show you the cumulative percentage of total sales up to and including that product. You can also select a bar to filter the rest of the report based on that product.
That’s it! You’ve now created a Pareto chart in Power BI using DAX measures. The Pareto chart is a powerful tool for identifying the most significant elements in a set of data, and it’s a great addition to any business intelligence report.
Remember that you can adjust the formatting of the chart as necessary to fit with the rest of your report – for example, you might want to change the colors of the bars and line, adjust the font size and style of the labels, or add a background color or border to the chart.
In conclusion, creating a Pareto chart in Power BI might seem daunting due to the complexity of the DAX functions involved. However, with a step-by-step approach and understanding of how each function works, it is absolutely achievable. This powerful visual tool can greatly aid businesses in identifying the key factors driving their revenue and making data-informed decisions.
