# When To Use TOPN In Power BI – A DAX Formula Review

This tutorial will review how to use TOPN as a virtual ranking function to generate interesting insights based on ranking logic in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

Using TOPN as a virtual ranking function allows you to dynamically produce the top and bottom results for any measure.

This example will show how to work out the locations that generate the highest and lowest revenue per customer.

Table of Contents

## Total Number Of Cities With Purchases

This table contains the Revenue for each customer.

You want to work out which locations have the highest and lowest Revenue.

Let’s say that a customer bought your products from a range of different locations.

Using Stephen Howard as an example, you can see that he bought products from six different cities:

You now want to know how much revenue was generated for each city. Then, you want to virtually rank the cities and calculate which of them belong to the top and bottom two.

This formula counts the number of locations a customer has purchased from:

The COUNTROWS function works out each unique place where a product has been bought.

However, this formula can’t give you with the ranking results you need.

This is where the TOPN function comes in. It allows you to have a virtual ranking inside a formula.

## Calculating The Top Two Cities

To calculate the top two cities with the highest revenue for each customer, you need to use this formula:

The CALCULATE function computes the Total Revenue using a different context for the top two cities.

Let’s now focus on the TOPN statement in the formula:

The first parameter for this TOPN statement is the total ranking that needs a virtual calculation. Thus, 2 is used to get the top two cities.

If it’s 4, it will return the top four cities.

You need to make sure that you’re only iterating through the places that a customer has purchased from. This is why Index is used rather than an element in the model.

Using Index ensures that you’re only counting the regions your customers have purchased from, and not all the regions in your model.

If you were to put the VALUES function along with the actual Name of the City, you’d get the overall Total Revenue from the top two cities — not from each individual customer.

The TOPN function creates a brand new context for each result in the table.

It’s creating a virtual table containing only the top two locations a customer has purchased from.

## Calculating The Bottom Two Cities

This is the formula used to calculate the bottom two cities:

It’s exactly the same formula as the first, but you need to change DESC to ASC.

Here’s how to check if this formula is correct:

If you bring the cities with purchases to the table, you can see that the number of iterations matches the result of the Total Cities.

For example, Aaron Bradley bought from four different locations. So, there are four iterations showing in the second table.

You can see the four different purchase amounts the customer has for each location.

If you calculate and compare the figures of the two tables, you will see that they both match. All of Aaron Bradley’s amounts equal to 173,128.00, which is the Total Revenue. The top two cities have a Revenue of 124, 191.20, and the bottom two 48,936.80.

## Conclusion

This tutorial discussed how to use TOPN as a virtual ranking function to create effective ranking visuals in Power BI.

You can wrap the TOPN function in COUNTROWS, SUMX, or AVERAGE to create more valuable insights in your reports. It’s a very flexible and reusable tool to use.

All the best,

Sam

[youtube https://www.youtube.com/watch?v=xT6xPr5mLaE?rel=0&w=784&h=441]

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Advanced Power BI: How To Create Multi Threaded Dynamic Visuals

In this post, I want to show you what is, without a doubt, one of the most compelling visualization...

## Showcase Insights Using The Multi Threaded Dynamic Visuals Technique In Power BI

This tutorial will cover how to use the Multi Threaded Dynamic Visuals technique to create insights...

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Calculate Rolling Totals Using DATESBETWEEN In Power BI

Here I’m going to show you how you can calculate the amount sold between two different dates using the...

## Formatting DAX Code In Power BI

Effort here will pay dividends later Placing effort into formatting your DAX code will make a huge...

## Cumulative Totals Based On Monthly Average Results In Power BI

Today, I wanted to cover a unique technique around cumulative totals based on monthly average results...

## Power Automate Expressions – An Introduction

In this tutorial, we’ll explore Power Automate expressions and how to use them in workflow automation....

## List.Max Power Query: User Guide With Examples

One of the most powerful and useful functions in Power Query M language is List.Max. It can be...

## R Scripting For Power BI Using RStudio

In this blog, we’re going to go through the basics of R scripting for Power BI using RStudio. The...

## Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

## Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, you’ll learn the general strategies to improve your DAX or M capabilities. This is an...