# 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

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...

## Understanding Data Models and Visualizations

Power BI is a robust and versatile data visualization tool that has gained popularity for its...

## Getting Started with DAX in Power BI: A Beginnerâ€™s Guide

Data analysis expressions (DAX) are the key to unlocking the superpowers of Power BI. If you want to...

## Building a Data Visualization Portfolio – Showcasing Your Skills and Insights

Data visualization is a powerful tool that allows you to communicate complex information in a way that...

## Guide to Intermediate DAX Functions for Power BI

In todayâ€™s data-driven world, being able to use data analysis expressions (DAX) in Power BI and other...