DAX TOPN

This blog post will outline the Power BI DAX TOPN function. The DAX TOPN function is a very practical useful function as we often want to see the top performing products by sales, region or shop.

TOPN syntax

TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])

Parameters

n_value
The first parameter will determine the number of rows that will be returned. A single scalar value will be returned where the expression will evaluate multiple rows.

table
This is the table from where you would like to evaluate the TOPN values.

orderBy_expression
This may consist of any DAX expression by which result of the expression will sort and evaluate each row of the table.

order
(Optional) Will sort your expression by ascending or descending order.

Practical DAX TOPN function examples

The TOPN function is best used within other DAX functions. Let’s start off with a really simple example. We want to calculate the Total Revenue of our Top 10 Sales each year.

We will use the following measure:

Top 10 Total Revenue = CALCULATE([Total Revenue],TOPN(10,’Sales Records’,’Sales Records'[Total Revenue],DESC))

And we will do the same for our ‘Total Profit’ and ‘Total Cost’:

Top 10 Total Profit = CALCULATE([Total Profit],TOPN(10,’Sales Records’,’Sales Records'[Total Revenue],DESC))

Top 10 Total Cost = CALCULATE([Total Cost],TOPN(10,’Sales Records’,’Sales Records'[Total Revenue],DESC))

These measures will return the revenue, profit and cost of our TOP 10 sales for each year. Let’s put these measurements into a table and see the results:

What we see here is that when we throw this into a table the TOPN filter calculates the top 10 revenue, profit and cost of our TOP 10 sales for each year.

Let’s create another example where we want to see the Total Sales of the top 2 products for each region. We will make use of the following calculation:

Top 2 Products per Region =
VAR
ranking = VALUES(‘Sales Records'[Item Type])
Return
CALCULATE([Total Revenue],
TOPN(2,ALL(‘Sales Records'[Item Type]),[Total Revenue]),
ranking)
We have just included a snapshot of the DAX measure as in Power BI so that you can see the difference colour in our DAX function.
Power BI DAX TOPN
We first created a variable called ‘ranking’. Note the difference in colour to our variable name ‘ranking’ and our DAX functions. The VAR function stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. In our variable, we used the function called VALUES. The VALUES function remove duplicate values and only unique values are returned, thus we have our unique Item Type names. Now that we have our unique Item Types we want to return the top 2 products Total Revenue per region. Again we used the DAX TOPN function in another DAX function, CALCULATE.
The result of our measure is as follow:
We can see what our DAX function has done. It only returns the top 2 items per region by Total Sales. Something to note is that it only returns the top 2 items and the rest of the items is empty. We are just going to order our table alphabetically by region to view this more clearly.
TOPN Power BI DAX
We see that only the top 2 items revenue is showed for Asia.

TOPN Dynamic function

Now that we know how the TopN functions work we are going to look at an example of how we can use the TopN filter to dynamically choose our TopN value so that our value is not hardcoded as for instance our top 5 or our top 10. So basically what we are aiming towards is to be able to see data of our top 5, top 10 etc without having to change our measure.

What we first need to do is to create a new table called TopN. We will just add a manual table with the values as indicated below:

This created a table with values that we can use for a dynamic TopN filter. This can be done by creating a measure that indicates to us what N value has been selected. To do this we will use the SELECTEDVALUE function.

N Selected = SELECTEDVALUE(‘TopN'[TopN Value])

What the ‘N Selected’ measure will do is to point to the N value filter that is selected. It is good before we go furthure to clarify why we had to create this measure. Our ‘TopN’ table cannot be connected to our ‘Sales Record’ or any other table for that matter as there is no unique identifier that we can use to connect the tables. This means what is necessary is what we call a disconnected slicer. The measure is then in essence ‘disconnected’ to the table, but it will provide us with a value for our TopN slicer. Let’s see what this will look like in a measure.

We are going to update our measures created at the beginning of the blog post, but change our static TopN filter value with our newly created dynamic TopN value.

Top N Total Revenue = CALCULATE([Total Revenue],TOPN([N Selected],’Sales Records’,’Sales Records'[Total Revenue],ASC))

And we will do the same for our ‘Total Profit’ and ‘Total Cost’:

Top N Total Profit = CALCULATE([Total Profit],TOPN([N Selected],’Sales Records’,’Sales Records'[Total Revenue],ASC))

Top N Total Cost = CALCULATE([Total Cost],TOPN([N Selected],’Sales Records’,’Sales Records'[Total Revenue],ASC))

Let’s now replace all our values in our graphs with the above measures and see how our data updates with selecting the TopN filters. The first visual indicates the value selected as top 10:

 

The next visual displays the top 50 values selected.

This is really useful as we can view our topn values dynamically. Another amazing function by Power BI that is essential to have in your toolbox for powerful visualisations.

See post great posts here.