Pareto Analysis, popularly named as the 80-20 Rule is an effective strategy to look into the relativity across the causes of a problem. There may occur scenarios in business, when 20 percentage of the group would provide 80 percentage of the major impact and 80 percentage of the group impacting the least 20 percentage of the scenario.

The Pareto Charts allows us to compare the effects caused by these groups.

Assume a Pareto chart displaying the dimension members that cause the cost incurred in an organization. The members are sorted in the descending order and the percentile spend contribution in a cumulative sequence. To measure the relative impact a threshold value is needed.

Power BI is a Self – servicing BI tool and it does not have a native visualization to display a Pareto Analysis.

The equipment of precise DAX scripts for the data is required for Pareto Analysis. Assume a table with simple data as follows:

This is the data consumed within Power BI.

The following are the steps to create the Pareto Chart.

Step 1: Create a calculated column displaying the cumulative values of the Measure using DAX scripts.

Step 2: Create a calculated column for the percentile of the cumulated values with the following script

Step 3: Create a calculated column for maintaining a static threshold value.

The final Pareto Chart would appear like this when the required fields are dragged into the Line – Clustered Column Chart.

Add On:

In addition to the cumulative functionality, the running totals separated by certain intervals can also be obtained using the DAX scripts. For Example, a cumulative value for every quarter or semi-annual period of the fiscal period which might be a financial requirement of an organization, can be calculated.

The highlighted values are the cumulative for 6 months and 12 months respectively.

Script:

This Line – Clustered Column chart allows N number of fields being displayed as Line/Column charts. This way, such intrinsic analysis can be obtained using the DAX scripts in Power BI at ease.

Got questions? Click here to reach out.