Dynamic TopN & Ranking in Power BI

Scenario

Let’s assume we have data about projects, and we want to rank the projects by budget, so that if we filter the data by some criteria (i.e. region, country, or some other classification) the ranking recalculates for the new scope. We also want some metrics calculated for the TopN projects, i.e. total budget for TopN and percent to overall budget. But most importantly, we want TopN to be a dynamic selection, meaning that in using the report, we want to focus on Top5, or Top10, or Top15, etc. by changing our selection at any time.

HowTo

Power BI Desktop supports DAX for calculations and data analysis problems. DAX (Data Analysis eXpressions) is a collection of functions, operators, and constants that can be used in formulas or expressions to calculate and return values. A couple of those that we’ll use here are RANKX and TOPN (see the full DAX Function Reference here).

So, we need to rank the projects first. Then we need to implement the option’s selection of the TopN. Then we define the metrics. And finally, we put everything together with some charts & tables using Power BI Desktop.

1. Ranking the Projects

We will rank the projects by total budget, so we need a “Total_Budget” (with an underscore) measure (not a field, as we need some aggregation), based on the “Total Budget” (without underscore) field… both of these from the “Budget” table:

Total_Budget = SUM(Budget[Total Budget])

On the table of Projects, we create a new measure by right-clicking the “Projects” table on the right-hand pane for Fields (or clicking the “…” to the right of it):

And we define the measure (named “Rank” in this case) as follows:

Rank = RANKX(ALL(Projects), Budget[Total_Budget], ,DESC)

• As this measure is on the “Projects” table, the default context for the measure will be the row for which it’s being calculated… and we don’t want the ranking to happen only for that row, but for all the Projects (hence the “ALL(Projects)” as the scope for the ranking).
• Then we define the criteria for ranking the projects; in this case, the total budget for each project that comes from the other measure in the “Budget” table indicated above, with the filtering context of these two tables being related to each other (there’s a relationship between Projects and Budget thru ProjIDs).
• And finally, the bigger the budget, the first to be ranked… so it’s in DESCending order.

Now we can use this measure in a table with other Projects’ fields, and order by it in ranking order by descending total budget.

2. Option’s Selection for TopN

We want to allow the selection of TopN, where N could be 5, 10, 15, 20, etc. So, we can use a couple features of Power BI Desktop that allows us to create a table by entering our own data (manually or copy/paste from i.e. Excel). This feature is called “Enter Data” in the “External Data” group on the “Home” ribbon:

Which opens the “Create Table” dialog, in which we can name the table, create columns, and enter data for it (or copy/paste from i.e. Excel):

The “Load” button will create the table; the “Edit” button will open the table in Query Editor in case we want to make additional modifications.

How will we use this table? We’ll add a filter visualization to the canvas, so these values will be filter options for us to choose from. From the runtime perspective, when we filter a table by a slicer, only records where the criteria is true will be in context, cross-filtering other tables related to it. In this case, there’s no relationship to other tables, but we can create a measure in this new table that will hold the value that was chosen, and then we can use it in other measures’ calculations later… so, we create the following measure on this new “TopN Options” table:

• The “MIN” function will ensure that in case the user selected multiple options, only the first will be considered in subsequent calculations.

3. Metrics for TopN

Now that we have the selection of TopN, we can create a couple measures on the “Projects”:

• This calculates the total budget for the TopN projects.
• The TOPN function takes the value for N (calculated previously from selecting it in the filter with the previous step).
• Then it takes the Table to get the TopN from, and the criteria for deciding what the TopN are.
• This function has some similarities with RANKX, in that it calculates the rank of the projects based on total budget descending, and selects the TopN (instead of just returning the rank’s value).

The second measure will calculate the percentage of this total budget for TopN projects, against the overall total budget:

Another measure we can use for displaying on tables, relates to highlighting which projects are the TopN based on current selections, by returning an arrow we can use as a field in the table for the TopN projects:

• If the rank for a given project is less or equal to the TopN value selected by the user, then an arrow will be returned for display/highlighting in a table.

4. Using all these with Charts and Tables

Here’s how we could display some of these measures and fields:

• The filter is an Slicer configured as:
  
• The two metrics are Cards configured as:
  
  
• The bars are Stacked Bar Chart configured as:
  

Then we could have a table with fields from Project and Budget and some of these new measures, ordered by the Rank column:

Which is a Table visualization configured as:

So, if we select Top15, it all gets filtered accordingly:

Taking advantage of Power BI’s cross filtering, if we select the bar for “Asia”, all other controls will get cross filtered, the ranking recalculated to this new subset of projects, and we effectively get the information for the “Top15 projects in Asia”:

A final example for the “Top10 projects in Europe”:

Which tells us that “the Top 10 projects in Europe represent 31.7% of the total 32MM Euros (the total of the table’s column for budget) across all projects there, amounting to 10.2MM Euros, where the Top3 reach about 4.8M Euros (the first three rows)”.