Dynamic Pareto Charts in Power BI - revisualized Part 1

[cfriss]

We know the Pareto Principle — 80% of consequences come from 20% of the causes. Or, 20% of your customers account for 80% of your sales. Et cetera. We see this typically displayed with bar charts and a cumulative percentage line stretched across. Most information on how to do this in Power BI does not allow the chart to by dynamic, meaning the pareto percentages do not recalculate as filter selections are switched — they are fixed as calculated columns and static in the visualization. For example, we might want to see our top features in different timespans throughout the year, or top features for different products we select.

I will take you through how to create a reimagined dynamic pareto chart using only measures. We will cumulatively rank app features (or any other categorical items) based on the number of clicks without creating additional columns. This way, the pareto chart can dynamically update as filters are used in the dashboard. I had found multiple examples to make static pareto charts using calculated columns, but this did not fit the needs of my business. We needed to dynamically compute top feature usage across a variety of products, over time. And because my product manager wants it all, we diverted from the standard pareto chart (So boring! Such space!) into KPIs and Stacked Bars to show percentages instead of cumulative distributions. It saves a lot of real estate and gets to the point quickly. I was able to engineer a full solution from several partial solutions that were available. My result looks like this, with filters that allow my top features to dynamically update.

Quick kudos: this linked tutorial for dynamic pareto functionality was the basis for what I used to create the running cumulative percentages. If you prefer the traditional Bar and Line pareto chart it can easily be created by the end of this tutorial and look like this, with top features highlighted in purple:

There are 4 main phases to create these charts

1. Get cumulative running totals and percentages
2. Identify top features vs all others based on the threshold you set (80% here)
3. Get counts of those features (top and all others)
4. Create visuals

*********************************

Cumulative Totals and Rankings

I like to have my data in a table so I can evaluate weather the measure is behaving properly. Let’s create some measures.

Create a measure for the basis of the rank (TotalClicks) and add it to a table with your categories. Sort this descending by TotalClicks.

Create a Rank Sequence.

 

The tricky thing here is that the rank measure can sometimes tie when filters are used. Even though we know there isn’t really a tie. Feature 1 and Feature 2 are clearly not equal below. The tied RankSequence will cause the cumulative totals to add up incorrectly, so we need a tie breaker measure.

Create a Tie-Breaking Alphabetic Rank measure.

Create a new sequence that uses the Alphabetical Rank as the tie-breaker.

Drag these into our table and ensure that truly unique ranks, in order, are occurring. Don’t worry about the numbers having gaps. What matters is that they are unique and the final RankSequenceAlphabetical values are in sequential order.

Check the filters and see if you can get a tie to occur on the RankSequence measure. Now the tie is also broken in the new measure when using the filters. Notice that Feature 2 is now the top with 211 clicks, and Filter 1 has 157 clicks. Its RankSequenceAlphabetical has pushed it below Feature 2. This is the correct behavior!

Create a measure to use the new ranking to get cumulative running totals.

Get the overall total of clicks — this will be a fixed number based on what is selected in the filter. The cumulative total will be the numerator, this value is the denominator for getting the cumulative percentage.

Get the Cumulative Percent — a simple division.

Pull all of these onto the table. Here is what it looks like. Notice that the TotalClicks_All repeats the aggregate total (2,786) for every row. The cumulative percent can be calculated on each row, and the cumulative total events is cumulatively adding clicks in descending order.

And when we use a filter, the cumulative percentages re-compute correctly, as do the rest of the measures.

Identify top features vs all others based on the threshold you set (80% here)

Now that we know our percentages, we need to establish a cutoff so a “top feature” can be identified. Another tricky thing is that one or more features might not always account for exactly 80%. Sometimes one large feature will come close, and the addition of another feature will push the total percent over 80. We make some ground rules in our measures.

Identify features accounting for up to, but not more than, 80%. If a feature does not fall into this constraint, it is assigned a value of 0.

Find the cumulative percent that is the smallest. This helps us identify if one large feature pushes the cumulative percent over 80. Then we want only that feature as the top feature.

Use these two measures to indicate if a feature is a “top feature” or not (return blank for those that aren’t).

Set these in a table and verify we are flagging top features when our cumulative percentages approach 80% but do not go over it. Below, we see that two features (5 and 3) were flagged as Top Features — both of them account for nearly 80% of clicks:

With some filters applied, we can see that now only one feature accounts for the majority of clicks (Feature 5 at 84%). Since this is the smallest cumulative percent that is over 80%, and it’s value matches the CumulativePercent_MinAll value, only this feature is flagged.

Last example, using different selections, we see that two features are flagged (Features 3 and 1), accounting for 61% of usage. This is not as close to 80%, but as we can see, adding in a third feature would push our cumulative percent to 88%. So we stay with the two features that make up ≤ 80% only.

Please See Part Two for the next phase. 

ParetoFile.pbix