Top N functions – Creating Measures Using DAX

Top N functions

There is nothing in the RANKX function to limit the number of rows returned. We will see in this section how to limit our calculations to the top N.

We will start with the easiest way: just asking.

Q&Aing our way to a top N list

As we saw in the previous chapter, we can use Power BI’s impressive natural-language capabilities to help us create reports. Using the Q&A visual, we can ask Power BI to answer the question What are my top 5 product names by gross revenue?

Figure 7.17 – When you are not certain how to do something in Power BI, just ask!

And with zero coding on our part, we get a top 5 chart!

Top N filter

In the filters pane, there is an option for a top N filter on a visual. Click on the visual you want to filter and select the category you want to filter on.

Then, in the filters pane, expand the Filter type list and select Top N. In the Show items settings, select Top and however many you want to display. Then, select a numeric column or measure as the value that you want to filter the field by. The visual updates accordingly.

Figure 7.18 – Using the filters pane to limit a visual to the top 5! Easy!

We can use this type of filter to limit our RANKX functions as well, though it would be easier to create a filter on the RANKX field and limit it to 5 or 10 or whatever we choose.

Top N function

Another option to calculate the top 10 products using DAX is to use the TOPN function. Using TOPN, you can discover the top 10 values in different filtering contexts, such as the impact the top 10 products made on the total sales.

The TOPN function returns a table, not a single scalar value, and you can use it as follows:

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

The first value is where we tell TOPN how many rows we want in our table, while the next is the table we want to run TOPN over. This can be any valid DAX expression that returns a table, or just a table. orderBy_Expression is the value we want to order our table by, and the order is the direction, descending or ascending, that we want the table to be ordered in. We can repeat orderBy_expression and order if we want to use more than one expression in our calculation.

Here is an example where we use TOPN to generate a table to filter our data by:

Top Product Gross Revenue =

CALCULATE([Gross Rev],

TOPN(1,ALL(‘Product'[Product Name]),[Gross Rev],DESC)

)

We can use this calculation in visuals or other calculations. We can use this in another measure to compare how other products compare against our best-selling product:

% Product Gross Revenue vs Top=

DIVIDE(

        [Top Product Gross Revenue],

        SUM(Sales[Gross Revenue])

     )

We can now create a visual comparing our top product revenue versus everything else.

Figure 7.19 – Comparing our top product gross revenue against all our products

TOPN is a great way to compare parts of your data against the rest of the data. Next, let’s look at how we can use semi-additive measures to enhance our data model.

Leave a Reply

Your email address will not be published. Required fields are marked *

Shopping cart

close