How to do Pareto Analysis in Power BI using DAX

How to apply the 80/20 rule to analyze the Data using Power BI DAX
Pareto Analysis uses the Pareto Principle, known as the "80/20 Rule", which was introduced by the Italian economist, Vilfredo Pareto.
Pareto Analysis is a statistical technique in decision-making used for the selection of a limited number of tasks that produce significant overall effect.
The Pareto Principle states that 80 percent of a project's benefit comes from 20 percent of the work. Or, conversely, the 80 percent of problems can be traced back to 20 percent of causes.

This technique is also called the vital few and the trivial many.

We can Improve the Business / Process performance by Identifying and resolving the Vital 20 percent of the reasons that causing problems which impacting our 80 percent of the Business outcomes.

Lets discuss on how we can analyze the data using Pareto Analysis, using below example.

Scenario :

Suppose, We would like analyze, what are the vital reasons that Causing the Sales Orders are getting revised in the Business, which has Impact on our Business Processes(eg. O2C).

In a Business Scenario (e.g.O2C / I2C /P2P Process) , the Orders could be revised, because of the following reasons (just assume that):

--Incorrect/Change in Order Quantity,..

--Incorrect/Change in Shipping/Billing/Delivery Address,..

--Incorrect/Change in Shipping/Billing/Delivery Date...

--etc.

The data model as follows..

Dim_Revison_Reasons :

This Dimension table contains the reasons behind the Orders Revision.

Fact_Order_Revisios :

This Fact table contains the Orders that are revised. The sample looks as per below.

Method - I :

Now we do the required calculations using DAX, to do the Pareto Analysis. The Calculations are based on the Column "Revison Code" of the same Table, FACT_Order_Revisons.

Sum_Orders = SUM(FACT_Order_Revisons[Order_Value])

Total_Orders = CALCULATE(SUM(FACT_Order_Revisons[Order_Value]), ALLSELECTED(FACT_Order_Revisons))

Cumulative_Sum = VAR vSum_Orders=SUM(FACT_Order_Revisons[Order_Value])

VAR vRevisonSummary=

SUMMARIZE(ALLSELECTED(FACT_Order_Revisons), FACT_Order_Revisons[Revison Code], "Sum_Orders", SUM(FACT_Order_Revisons[Order_Value]))

VAR vCumulative_Sum=SUMX(FILTER(vRevisonSummary,[Sum_Orders]>=vSum_Orders),[Sum_Orders])

RETURN vCumulative_Sum

Cumulative_Ratio = DIVIDE([Cumulative_Sum],[Total_Orders])

The result of the above Calculations is as follows. The Calculations are based on the Column "Revison Code" of the same Table, FACT_Order_Revisons.

Now we can generate a Pareto Chart, using these Measures on the Line and Stacked Column chart.

Notes :

The Fields/Measure needs to place as per below:

The Secondary Y-Axis needs to enable, if in case it is not. And make sure to choose the Start values as 0 and End value as 1.

Finally, format the Data Colors (Line color; Bars colors using rules) as per below :

Method - II :

In this Method, we will just look at the other ways of doing the above discussed Calculations, when the Column "Revison Code" is from related Dimension table, Dim_Revison_Reasons.

Sum_Orders = SUMX(RELATEDTABLE(FACT_Order_Revisons),'FACT_Order_Revisons'[Order_Value])

TotalOrders = CALCULATE([SumOrders], ALLSELECTED('Dim_Revison_Reasons'))

Rank_Orders = RANKX(ALLSELECTED(Dim_Revison_Reasons),SUMX(RELATEDTABLE (FACT_Order_Revisons),FACT_Order_Revisons[Order_Value]),,DESC)

CumulativeSumX = SUMX(TOPN([Rank_Orders],ALLSELECTED(Dim_Revison_Reasons),[SumOrders]),[SumOrders])

The Cumulative Sum can be calculated using the following expression as well.

CumulativeSum = CALCULATE([SumOrders],TOPN([Rank_Orders],ALLSELECTED(Dim_Revison_Reasons),[SumOrders]))

CumlativeRatioX = DIVIDE([CumulativeSumX],[TotalOrders])

The result of the above Calculations are as follows :

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------