# 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.

## 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.

Cheers,

Sam

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Calculate Rolling Totals Using DATESBETWEEN In Power BI

Here Iâ€™m going to show you how you can calculate the amount sold between two different dates using the...

## Formatting DAX Code In Power BI

Effort here will pay dividends later Placing effort into formatting your DAX code will make a huge...

## Cumulative Totals Based On Monthly Average Results In Power BI

Today, I wanted to cover a unique technique around cumulative totals based on monthly average results...

## Power Automate Expressions – An Introduction

In this tutorial, weâ€™ll explore Power Automate expressions and how to use them in workflow automation....

## List.Max Power Query: User Guide With Examples

One of the most powerful and useful functions in Power Query M language is List.Max. It can be...

## R Scripting For Power BI Using RStudio

In this blog, weâ€™re going to go through the basics of R scripting for Power BI using RStudio. The...

## Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

## Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...