Discover Unique Insights Using Power BI TOPN Function

In this video, I wanted to find an insight that was really unique, and hugely valuable for business. Here, I used the Power BI TOPN function. You may watch the full video of this tutorial at the bottom of this blog.

I wanted to know the best-selling day for every single product.

Not only that, I wanted to be able to look dynamically into any store and analyse the best-selling day for every single product that each store sells.

Being able to extract this type of detail at a granular level is a really powerful analysis, and you can showcase it in a really dynamic way.

In this video example, I run through how you can utilise iterating functions – specifically TOPN to uncover this sort of information, which you can use to get some solid data to ultimately help you sell more.

The great thing about this type of analysis is that we can compare why some products sell better in certain regions on particular days versus other regions, where they might sell better on another day.

We can attempt to understand why that is and maybe offer promotions or discounting around those particular trends or behaviours within those particular stores.

Table of Contents

Working Out Average Sales Per Day

Before we workout the more advanced logic, we’re going to calculate the Average Sales Per Day.

On average, how much of these products do we actually sell? So let’s create a formula that enables us to calculate that.

This formula is very similar to another video on averaging, which is on Enterprise DNA TV. But here’s how we work that out.

Go and click on New Measure, and go type in the name, Avg. Sales per Day.

We will use the AVERAGEX function for this measure.

We’re going to iterate through every single day and calculate Total Sales, then average it up.

And so if we then drag it into our table, we can see that on average per day, this is how many we sell on each product.

And with this data, we want to actually see on what day is that average the best consistently.

In other words, we want to see on average, which day do we sell the most.

Calculating For The Best Selling Day

In this example, you’ll see how you can combine different functions and techniques and be able to retrieve the answer.

So we go and create a new measure, and we’ll call it Best Selling Day. In this formula, we’ll use the function, TOPN.

TOPN is a really cool function in Power BI. It enables us to create a table based on ranking.

So what we’re going to do is isolate, based on ranking, the highest day for average sales. Then, we’re going to try and return that day.

Here, we use MAXX, and it’s really interesting how we could actually return with MAXX a text value.

We’ll jump down into a new line and go TOPN. Since we want the top day, then this N value is going to equal to 1.

We then use SUMMARIZE in a new line. With SUMMARIZE, we’re going to reference a table, then a column, and the Day of Week is what we’ll actually going to iterate on.

And in the next line, we go Selling Days and put the Average Sales Per Day. Note that this is a virtual table we are creating.

Next, we are going to rank. In this table, we are going to extract the top 1 by referencing the Selling Days, which is actually the name of this column in the virtual table. Lastly, we go Day of Week.

With this formula, we could actually isolate anything. We could isolate not only the top 1, but the top 2, etc.

So when we go enter and drag it into our table, we can now see that it is evaluating the Average Sale per Day for every single week. Then, TOPN is ranking them, returning only the top 1.

We only return the top day of the week for this selling days calculation. That’s why we get the top day.

What’s also amazing is that we can actually click into a specific region. Let’s click into Florida, for example, and everything’s going to update.

We can see here that Tuesday for product 63 is actually the best selling day,

while throughout all our regions that we sell, it is actually Friday.

Things You Can Do With This Formula

There’s a lot of logic you could potentially add here, but the idea for this tutorial is to showcase that this is the logic that will get you here in the first place, and then you can branch out into different many ways.

You could automate your marketing based on this.

For instance, in a particular store, you have a consistent best selling day of something. Then, you could send out notifications every day, or send out an email marketing chain that will promote that particular product, say it is on sale, etc.

You can then run scenarios and see how much additional foot traffic in your store would increase the demand and sales of your other products.

There are so many cool things you could actually branch out into from this formula especially in the scenario analysis base.

When you combine it with this new Data Bars,

you could get a good picture of some really good insights. It’s going to generate a lot of value for you.

There are so many different ways that you could ultimately use the same technique. This is just one example.

From here, you can branch out into discovering the best salesperson for every single product; the best-selling product at each different store; and many others.

Conclusion

There are so many different ways that you could rework this formula and ultimately find insights into your customers’ behaviours.

If you can replicate this in your own datasets, it will become extremely valuable. I’m confident you’ll be able to find ways to replicate this over and over again in a lot of new models and reports you create.

As you get more into ranking strategies within Power BI, you can start opening analytical opportunities that you didn’t even think are possible.

I covered many of these in depth in the Advanced Analytics in Power BI on-demand course at Enterprise DNA Online.

To complete advanced analytics in Power BI, you need a solid grounding in formulas like TOPN.

But then you need to learn how to combine many of these formula to achieve some of the next level insight that I run through in this particular course. Check it out if you have some time.