Best and Worst: Two approaches to finding Top or Bottom N Percent using DAX

By Alison Box

DAX Power BI in Practice

Ever wanted to find which are your best or worst customers or best or worst products? Finding a simple TopN (or BottomN) in Power BI Desktop couldn't be easier; use "Ask a Question" on the Home tab; just type "Top 10 Customers by Total Sales as a Column Chart" or "Top 20 Products by Quantity as a Table" and see the magic happen!

But what about finding Top 10 Percent? As the number of customers or products grows over time, finding Top 10 or Bottom 10 may not be as insightful as finding the Top 10 Percent or Bottom 10 Percent. Try using Ask a Question to find "Top 10 Percent" and it just won't work.

So how do you go about finding Top or Bottom Percent?

To do this, there are two approaches; a basic approach that just finds a specific percentage (e.g. Top 10 Percent) or a more dynamic approach that allows you to select whether you want Top or Bottom and then which percentage you are after.

Note that the value you use in your Top or Botton N must be a Measure.  For example I've used a Measure called  [Total Sales].

Approach No. 1 – Fix the Percentage in a DAX Measure

To do this create a "Top 10 Percent" or "Bottom 10 Percent" measure as follows:

Top 10 Percent =

VAR Rank_TopCustomers = RANKX ( ALL ( Customers) , [Total Sales]  )

VAR Rank_To_Find = COUNTROWS ( ALL ( Customers) ) * 0.1

RETURN

CALCULATE ( [Total Sales] , FILTER ( Customers, Rank_TopCustomers <= Rank_To_Find) )


Bottom 10 Percent =

VAR Rank_TopCustomers = RANKX ( ALL ( Customers) ,  [Total Sales]  , , ASC)

VAR Rank_To_Find = COUNTROWS ( ALL ( Customers) ) * 0.1

RETURN

CALCULATE ( [Total Sales] , FILTER ( Customers, Rank_TopCustomers <= Rank_To_Find) )

Explanation

The RANKX function for Top 10% ranks the customers into Total Sales order, "1" being the customer with the highest sales and ascending in rank number to the customer with the worst sales. The RANKX function for the Bottom 10% reverses this order.

The COUNTROWS function finds the top N ranked customers depending on the percentage you want to find. This is done by taking the total number of customers and multiplying by the percent you want divided by 100. For example, if there are 84 customers, and you want to find the top 10%, then this expression would calculate 84 * 0.1 = 8.4. So you would want to find the top 8.4 customers.

The CALCULATE function filters out customers whose rank falls within this value i.e. 8.4

 

For example, if you have 84 customers, top 10% of these customers would be the top 8.4.

However, this assumes that you want to find the top TEN percent. What about Top 20%, 30%, 40% etc or even Bottom N percent?

 

 

 

Approach No. 2 – Use Slicers to Determine Which Percentage and Whether Top or Bottom

This approach requires two steps:

  1. Create slicers to select which Percentage and whether Top or Bottom
  2. Create the Measure for the Top or Bottom Percent

Create Slicers to Select Which Percentage and Whether Top or Bottom

To create these slicers, you need to create two tables, using the "Enter Data" button. I've called these tables "Percent" and "Top or Bottom". Both tables contain a single column (also called "Percent" and "Top or Bottom", as show here:

 

Note you will need to format the Percent column as Percentage (use the modelling tab and the % button in the formatting group).

Create slicers using the "Percent" column from the "Percent" table and the "Top or Bottom" column from the "Top or Bottom" table.

Create the Measure to Find Top or Bottom Percent

Now create this measure:


Top or Bottom =

VAR RankTopCustomers = RANKX ( ALL( Customers), [Total Sales] )

VAR RankBottomCustomers = RANKX ( ALL( Customers), [Total Sales]) , , ASC)

VAR RankToFind = COUNTROWS ( ALL ( Customers)) * SELECTEDVALUE ( Percent[Percent] )

RETURN

IF ( SELECTEDVALUE ( 'Top or Bottom'[Top or Bottom]) = "top",

CALCULATE ( [Total Sales], FILTER ( Customers, RankTopCustomers <= RankToFind ) ),

CALCULATE ( [Total Sales], FILTER ( Customers, RankBottomCustomers <= RankToFind ) ) )

Explanation

This works in a similar way to the first approach with the following differences:

The second RANKX function ranks customers by ranking "1" as the bottom rather than the top

The COUNTROWS function now multiplies the total number of customers by whatever percentage is selected the "Percent" slicer.

The IF function looks to see if you have chosen "Top" in the "Top or Bottom" slicer and will filter customers accordingly.

Now use this measure in your visual and slice by Percent and Top or Bottom:

You'll see you can dynamically change whether to slice by Top percentage or Bottom percentage, and can change the Percentage value

Benefit from expert training

At Burningsuit we have years of experience in providing expert, well-rated training courses in Power BI, DAX and Excel. We are adept at providing the information and skills you need to make the most of Power BI together with your data and offer on-site and public courses across London and the UK.

To contact us, please call on 0800 0199 746 or use our contact page. We're always happy to discuss Power BI training and implementation.

Leave a Reply