DAX Power BI: TOPN — Output maximum values in measure

Let’s look at another table function of the DAX language — the TOPN function. It allows you to create a table in which you can display the top values. This table function can be attributed to the filter functions, because this function takes the table and filters the rows, leaving only those that relate to a given condition.

Moving on to our data model, there is a simple table that contains orders. Let’s analyze the top 5 on this table: let’s calculate the top 5 sales and display the sums of sales on one side and the top 5 products by sales on the other side.

Create a table, name it Top5 and call the function TOPN.

In the first parameter of the function we need to enter a numeric value that will correspond to the number of rows that TOPN should return.

In the second parameter we need to specify the table by which TOPN will count the top value, we specify the Orders table.

In the third parameter specify the column from the table, which was specified in the previous step, or the expression calculated by the same table, in our case we need to specify the Total column.

In the fourth parameter we output the order of counting the top values, in our case it is Total, and since the greater the sum of sales, the better, we need to output the information from greater to lesser — this is the DESC parameter.

The table is created and we can see the orders with the highest Total value.

Let’s reconfigure this table to have the product ID as a slice. To do this, let’s use the SUMMARIZE function

Let’s create the table using this function and check the result

Now let’s insert the resulting code into the original table and get the top 5 by products