Let me show you how you can create a Power BI Top N report using a top 10 or Top N filter as a bonus
we will also compare it with another approach using the DAX Topn function now if you want to become a Power BI Pro
Make sure to subscribe and click that bell. So you are notified whenever I go live to answer your Power BI questions.
Let's say we're building a sales report and we have the high level numbers over here showing us the overall sales year of year change
And we have some breakdown by country as well
but we would like to get an understanding of
How much of that is coming from our top 10 customers and also be able to see those customers now?
Of course, I'm talking about a sales report
But the same concept can pretty much apply to any data set and any measure or KPI that you're tracking.
So let's go ahead and try to build this table.
So I'm going to start building it here and hey
As always you can go to learnpowerbi.com/download to get the finished file and try these out for yourself.
So let's start here where all I have is a simple table with the sales measure on there now
this is a pretty simple measure just a
Sum of sales amount as you can see over there and you can see that right now. I have this in my
In my table over here, and it's not really showing top 10
It matches the overall total sales that I have for the selected here which in this case is 2016
so let's add some more meat onto this and what we're gonna do is let's go ahead to the customer table and
Bring in the full name off the customer
So we're gonna have full name and I'm gonna use these later but I'm gonna add them, right?
Now the order count and the customer count and you'll see where we will use it later on
So I've added those measures but again right now we're seeing all the customers
So let's get started by filtering it down to the top 10 in our case
So I have this one selected and in here all the fields shown here are also available to me over here now
I could add a top and filter to the full name
But in this data set, we have two different customers with the same name
Which is possible now. Well, hence, what we're gonna do is in fact filter down to the customer key now
It's not perfect solution, but gets us close enough. It works for us in this scenario
So let's say I'm gonna add the customer key
So again, it's a it is not being shown in the table
But I'm gonna use the customer key to add the top 10 filtering
Now when you add this as a visual level filters this filter only applies to this
specific visual now if you did want to show the whole page or
Possibly all the pages in this report to just reflect the top 10 customers then
Instead of setting it up in visual level filter
You would add it as a page level or report level filter
but for us and this is
Exactly what we want this
So now let's go to the customer key and in here if you click on the drop down to advanced filtering
You would see the top end option right there
So we're gonna select top end and then you do have options for like top or bottom
So we're gonna say top and then it says well how many in this case we have 10 elements now
Of course if you just want to focus on your top 5 customers or something like that
You can set that here now often. I hit enter and I expect something to happen and nothing changes
in fact
the apply
filter button doesn't even light up because there's one field which is still
Added which is top 10 by what now?
Often when you're showing just one field on the on there like sales now
I did I did add other fields, but you might just have sales there
So to you, it might be obvious that hey I want top 10 by sales
But it's not obvious to power bi so we're gonna help it out and say yep, you know
Show me the top 10 customer buy sales, so I'm gonna drag that value over here
And again, sometimes I expect something to happen, but you still have to click the apply filter button down at the bottom
So I'm going to click apply filter and notice what happens over here
So now you can see we're only seeing the top 10 customer buys sales now
You don't obviously you can change it so you can say hey I don't care about the customer sales
Instead I would want to see the top 10 buy order counts
So I I drag that over here and now when I apply filter it's now
By top ten customers by order count now
Yeah
You you might notice that it's actually showing me more than ten elements and that because off there are ties here
We'll talk about ties more in this video as well
One other thing that I would emphasize here is that I can I don't have to show
The measure that I'm using to calculate the top ten so I can say show me the top ten customers
By their order count but not show that value in there at all
So I I just want to say hey show me the top ten customers
By order count and show me who they are and what their sales numbers are
But order count is only being used for filtering now you can do that
But I almost never
Do that because it ends up being a little confusing. So usually whatever measure I'm showing there
That's the one I use to to use the top end filtering so great
So now we can see our overall numbers for the complete year from all the customers
But we can also keep a close eye on our top are
besties our favorite customers our top ten customers
See how much they're contributing if you wanted to you could obviously
calculate this as a percentage of total sales what percentage of our sales is coming from top ten customers and
It has the magic of power bi which is you can slice and dice it any which way you want
so this is a worldwide customer, but you can say oh, I only want to see the top ten customers in US or
Australia or you know how to Kingdom on France and you get the idea
But now let's try to see it by month and you're gonna see something we had hit on earlier
So if I go on January then I see oh, these are the top ten customers in January. But watch what happens in February
It's showing us
eleven customers and he had seen the ties when we were playing around with order count as well so you can see it at the
Bottom we have a tie at the bottom a three-way tie it seems and that's why it's showing us not ten
but actually eleven customers and even
the total is
Not for the top ten because it just stopped down when there is a tie and includes all of them
It is showing counting up all of these
So watch out for that ties can be a challenge when you're trying to do top-end or top-ten filter
another challenge is if you want to see some kind of a trend of
contribution or just look at this contribution by top ten and just see it over time or
Across country or some other attribute now for both of these handling the tie scenario and showing trends the top-end
Dax function can come in handy so you can get
Download this file and examine the measure and how that's been built
But I just wanted to show you the end result and compare the two. So right now they look alike
so this is a Dax measure so sales, it's showing me the sales of top ten customers and I also
You know use this and and divided this by the total sales we had to calculate the percentage sales from top ten customers
Right, so that one the percentage sales is I simply just give me the sales top ten and say let's just divide 20.6% right?
So but the one of the first of all the power of these measures, is that now you have this number
You don't have to go about setting a visual level filter every single time
You can just use that to slice and dice that so you can see. Okay, cool
Well, what about the sales top top ten customers by country and you can see well
Australia Canada
France Germany how much in
absolute sales dollars is
Coming from top ten customers in each of those countries, right?
How top dependent have a top-heavy re and just to make it make it kind of apples to apples because United States
Would well does have a really big chunk of the sales in
in by country and
You know, we we kind of see that in this United States is a big block. It's fair to calculate that as a percentage
so here you can see that France is the most stop having 6.2 percent of their sales is coming from top ten customers and
United States
It's really really small point six percent and not just that we can also trend it over time and see how things have changed
So you can see that we started the year kind of a little top-heavy morale for sales revenue coming from the top ten customers
But then it has trended down. So that is interesting
And of course, you have the magic of power bi where you can slice and Dicers show me the trend for France
Show me the trend for Germany and you can see France
Trended down and let's see United States and that really trended down right so you can always slice and dice that so that's one big
advantage of using measures but let's go back to the challenge we had for theis now the scenario that has shown you was
January was fine. It was showing us kind of 10 top 10 customers
But as soon as we switch to February there was a tie at the bottom and that ended up showing us 11 customers
But you can see here that our Doc's measure is
calculating it correctly and you can do the math yourself if you like just
you know take the total to 17 through 7 and
Remove the last the 11th person to five-on-five and the answer would be what Dax is returning to you
So you've seen two approaches do let me know in the comments below
Which one do you prefer or tend to use and don't forget to download the file at learn power bi comm slash download
Hey keep watching more videos and keep learning power bi but if you did enjoy this video
I would love to hear from you. So leave a comment like subscribe
All the good stuff while we're on my friends