# 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.

## 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

## Power BI Slicers Tutorial: Counting Selections Correctly

In today's tutorial, I'm going to work through a subtle issue with Power BI slicers that can trip you...

## Time Intelligence In DAX: How To Dynamically Select Starting Period

In this blog post, we will deal with some troublesome issues in time intelligence, particularly those...

## Power BI Themes: User Guide With Examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various...

## RANKX Considerations – Power BI And DAX Formula Concepts

RANKX is a very unique DAX function that requires a deeper level of thinking around its logic and...

## Using Filter Fields & Visual Interactions To Create Compelling Visualizations In Power BI

I want to show you how you can use filter fields and visual interactions in Power BI to great effect....

## Show Cumulative Totals Unaffected By Date Slicer Selection In Power BI

In this blog post, you'll learn how to show data from specific time frames as filtered by a date slicer...

## Power BI Port Number: Connecting Power BI To SSAS, Excel, And C#

In this tutorial, you’ll learn how to use a Power BI port number to connect a data model to SSAS,...

## Sorting Date Table Columns In Power BI

In today's blog, I'll discuss a question that comes up all the time in the Enterprise DNA forum. How...

## Filter In Power BI: DAX Queries Context Transition

This post will discuss how to identify a filter via xmSQL and the storage engine query in Power BI....

## How To Work With Multiple Dates In Power BI

As soon as you start developing a few Power BI models, you'll very likely run into a problem where you...

## Power Platform Tutorials: Why Get A Microsoft Power Platform Certification?

Power Platform Tutorials For this blog post, I'll be talking about Microsoft certifications related to...

## Customer Analysis In Power BI; Reviewing Performance Over Time

Customer analysis is a crucial thing to do for any business specially if you have a large number of...