Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Fowmy

SOLVED! Pareto Chart With TIES In Power BI | Step-By-Step Tutorial | Part 2

Ties in a Pareto chart are when the same rank appears more than once. Creating a Pareto Chart is not difficult and this video will show you how to break down a complex Pareto DAX measure step by step for you.

If you haven’t watched Part 1, here is the LINK

Power BI lets you create Pareto charts without the need for any other custom visuals. The process is very straightforward and should be easy to understand with simplified instructions.

 

For this video, I created a dummy data set of guest complaints for a fictitious restaurant chain. We will use a Pareto chart and the 80/20 principle to plot and analyze our data and find out the top few vital issues that are responsible for the majority of the complaints.

SUBSCRIBE AND WATCH THE VIDEO

 

PBIX file can be downloaded in the video description

 

Hope this article/video was helpful to you? Please leave your comments, suggestions, or questions in the comments. 

 

Comments

Greetings @Fowmy , huge thanks for your video on Paretos with ties! I’m fighting with this right now. I’ve only been using PBI for three months, so my skills are pretty rough.

 

My data is numeric, which might be making it hard to reproduce your example using categorical data. But maybe the difference is simply COUNT versus SUM. Not sure.

 

Your data table had [date], [complaint], and [category].

My data table has [date], [User ID], and [Count of Stops]. (These are Operators pulling stock from warehouse racks to fill orders.)

Similar to your example, I have my data table linked to a Date table.

 

I did successfully build a Pareto using traditional language, but, as expected, it fails at the duplicates:

 

bdpaasch_0-1646340565630.png

 

 

(I clipped off the x-axis that shows User IDs (staff names) for privacy reasons.)

 

I’ve tried to recreate your example, here is your measure for easy reference:

 

Pareto % =

VAR __CurrentComp = SELECTEDVALUE( CompaintsData[Complaint] ) RETURN

IF( NOT ISBLANK( __CurrentComp ),

    VAR __AllselectedComp = ALLSELECTED( CompaintsData[Complaint] )

    VAR __CompTable =

         ADDCOLUMNS (

            __AllselectedComp,

            "@Count", [Complaints Count],

            "@Rank", RANKX ( __AllselectedComp, [Complaints Count] )+

                     RANKX ( __AllselectedComp, CompaintsData[Complaint])/1000000

        )

    VAR __CurrentCompPos = MAXX( FILTER( __CompTable , CompaintsData[Complaint] = __CurrentComp ) , [@Rank] )

    VAR __CumComCount = SUMX( FILTER( __CompTable  , [@Rank] <= __CurrentCompPos ) , [@Count] )

    VAR __TotalCumCount = CALCULATE( [Complaints Count] , __AllselectedComp )

    VAR __Result = DIVIDE( __CumComCount , __TotalCumCount )

    RETURN

     __Result

)

 

I did make a few changes:

I did not create a separate measure to SUM the Stops. I did that on the fly within the Pareto Measure.

I did not create a variable for my ALLSELECTED table. I was getting confused trying to keep track of all the variables. Anytime I needed my table, ALLSELECTED, I just called it again. (My table name is ‘RP DTD grouping of total stops’.)

I’m not concerned about zero values, those are filtered out during data cleaning. Therefore, your IF statement is gone.

I split your CompTable into two parts so I could better follow what was happening with the ranking steps.

Other than those changes, I THINK everything else tracks with your example. Here is my measure:

 

Pareto with ties resolved =

VAR CurrentUserID = SELECTEDVALUE('RP DTD grouping of total stops'[User ID]) RETURN

VAR RankingTableA =

ADDCOLUMNS(

    ALLSELECTED('RP DTD grouping of total stops'),

    "RankStops", RANKX(ALLSELECTED('RP DTD grouping of total stops'), [Count of Stops],,DESC),

    "RankIDs", RANKX(ALLSELECTED('RP DTD grouping of total stops'), [User ID],,ASC)/100000

    )

VAR RankingTableB =

ADDCOLUMNS(

    RankingTableA,

    "ComboRank", [RankStops]+[RankIDs]

    )

VAR CurrentUserPosition = MAXX(FILTER(RankingTableB, 'RP DTD grouping of total stops'[User ID] = CurrentUserID),[ComboRank])

VAR CummulativeStopsSum = SUMX(FILTER(RankingTableB, [ComboRank] >= CurrentUserPosition),'RP DTD grouping of total stops'[Count of Stops])

VAR TotalStopsSum = CALCULATE(SUM('RP DTD grouping of total stops'[Count of Stops]),ALLSELECTED('RP DTD grouping of total stops'))

VAR RunningResult = CummulativeStopsSum/TotalStopsSum

RETURN

    RunningResult

 

I did drop the section where I built the two virtual tables, RankingTableA and -B, into a New Table to see what the virtual tables look like. I did a RETURN with the two tables independently, they both look fine.

 

However, the visual did not work. Here is the same data as above, but using the new pareto measure:

 

bdpaasch_1-1646340565638.png

 

 

Can you spot what is wrong with my measure syntax?

Thanks!!!

@bdpaasch 

Glad the video was helpful!

Kindly share a sample or a dummy Power BI file that represents your scenario with your measures explaining the expected results. I will be able to look into it and suggest or help with a solution. 

Thanks

Hi,

 

Thanks for this solution. It is working fine but now experiencing below issue. Pareto percentage is exceeding 100. 
Pareto_Issue.png