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))
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:


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.
Hi
Thanks for your helpful information.
I use the Selectedvalue function instead N_Value in TOPN for the creation of a Dynamic Table, but don’t work it.
Also I use a variable instead N_Value and don’t work!
Table =
var N_var = Max(SlicerNum[index])
Return
(TOPN(
N_var,
(SUMMARIZE(
‘Table2’,
‘Table2[Column1],
“Test”,
(CALCULATE(
SUM(‘Table2′[S1])/(‘Table2′[S1]),
”Table2′[S2]>10))
)),
[Test],
ASC)
)
🙏🏻