Let me show you how you can create a Power BI Top N report
But with a variable end that means you can choose to show either your top 10
customers or top 50 or top hundred a really any
top or bottom number that you like
This is part of our top n playlist where we would be covering lots of different topics
around the top n theme you would be able to access the playlist at the end of this video now if you would like to become
A Power BI Pro make sure to subscribe and click that Bell icon
so you're notified whenever I go live to answer your Power BI questions in the first video in this series
We have seen how we can filter down to our top 10 customers and we've also seen how we can do an analysis on
The revenue that the top 10 customers are bringing in and this was pretty cool
So once we have this measure defined we can slice and dice it any which way we want by country by product
So to see how top-heavy they are
For example, we could say hey what has been the trend off the top 10 customers revenue for France again?
You can watch that first video as part of the top-end playlist, but now our goal is to do the same analysis
But we want to be able to very to choose
Sometimes we may want to see the top 10 customers maybe for other markets
We would want to see top 50 or top hundred or any other number like that
So, let's see how we can get to do that
So here I have the top 10 analysis, but right now it's hard-coded to only be for the top 10 customers
So what I'm gonna do is just duplicate this page and get started on
The variable n now the first thing that I need to do
Is that provide power bi?
so that
somehow I
can send it that signal that I want to see the top 10 or top 50 or top hundred or something else now how probably
I cannot read my mind. In fact pretty much the only way power bi gets its data from or my tables, hence
I'm gonna create a table
in fact
If you look at your report besides maybe text boxes images and shapes
Every single thing that is being shown here comes from the table. So that's our first task
We're going to create a table
Which can send that signal to power bi to say top 5 10 50
Now the easiest way to do that is just hit this enter data button right here. And that's what I'm gonna do
so click enter data and this brings up a dialog box here and
let's say
select n
And we're gonna fill in our options for now. I'm gonna say 5 10 20 50 and hundred and we'll
Stay with that and we'll call the table
Select top n and I'm gonna hit load and this is gonna be loaded into my model now
this is going to be a table and
What we're gonna do next is define some measures to see which n has been selected
So now the stable is loaded up here now I can put this on there and make it a slicer
Let's changes to a list slicers. We have five ten twenty fifteen hundred
But right now it doesn't have any effect on that now we want to obviously change
That so that this impacts this drives this report
So the first step for that would be to create a create a measure which tells me which n has been selected
So I'm gonna go to modeling and click new measure right there. Now. This is for those who are familiar this approach is called
Disconnected slicer because we're not going to establish a relationship between this table with the other table
But still in a way it's acting like a relationship
Almost like a dotted line because it's filtering the same way in relationships you alright. So we'll say selected n and
Let's see what we can do here selected n equals and I'm just going to use one of the built-in functions selected value
Select values that work
Select add value top
Select n that's the one that I'm looking for. Perfect. I'm gonna hit enter
Zoom out and and now let's see if we can make some space so we can see that measure
So as we have selected n, I'm gonna bring it up here
Okay, dragged it over there change it to a card. So there we go
It's working and and of course if nothing is selected here
It's gonna be blank and I can build some default behavior if I want to like default to 10 or something like that
but for now
I'm gonna leave it this so right now it's affecting this but it's only affecting this
What I would like to do is to have that selection affect the entirety of this report
So that's what we're gonna work on next
now we had already built the top 10 measures and they are already there in the model now if you realize
That the way the top 10 is written
The only part that we actually need to change is this one and instead of hard-coding it as 10
All we need to do is simply use the selected end value
so what I'm gonna do is I'm actually gonna
Copy this measure over here and cria put this in this new table that I just created
and I'm gonna say new measure and
All I'm gonna change is I'm gonna make a generic top n not a top 10 and the N is gonna be fed
by that table
So you see all I did was well change the label here so that because it's not top 10 anymore and instead of 10
I say you won't choose whatever value has been selected in there. So it's as easy as that
So that's my first measure I'm also gonna grab the percentage sales and change that to a generic measure
So I'm gonna say percentage sales for top n and a measures is all about reuse
so once I've defined the top n all I can do is
Just change that to use it here, right?
so I change top 10 to top and just the name and here I'm gonna use the new measure and
That's it I'm gonna hide this column I don't really need this this table is just holding my measures
All right. So what I'm gonna do is
Right. Now, of course, it's still not doing anything because I'm still using the old measures
So what I'm gonna do is I'm gonna snap my finger and I'm gonna change all my measures do not use this sales top 10
Top top 10, but use the more generic top n measures, right?
So I'm gonna I'm gonna swap it remove these ones and use these ones and it's gonna happen in three two one
All right. So all those measures have been changed
So all I did was just to remove the top ten measures and drag this new ones in now
Of course right now you notice any difference because the sales end is actually selected at top ten
But now the complete report report is being driven by that I'm gonna delete this. We don't really need that anymore
That was just for debugging and now we can say hey show me
How much revenue has been coming in from my top ten customers?
Ooh, what about top five Oh, what about top twenty. Now? One thing I noticed right away is that this was hard-coded?
This was a text box. So let's go and change that. So what we're going to do is instead of this
We're gonna create a text measure
so we're gonna go back to the selected end and I'm just gonna say new measure and we'll say selected n
label and we'll we'll we'll say top so we want to show that and then ampersand and we'll
bring in the selected value and we'll say
Oops, if I can type and analysis perfect, and now we can bring that in
Change that to a card. Alright great. So I change the formatting a little bit but now that a label is dynamic as well
So we can be looking at top ten and see the percentage of revenue has dropped through the year earlier. We were getting
3.4, you know between three and four percent and then at the end of the year it was down to around 1.5 percent if you
Look at the top five customers this shows the trend for that
This is the top twenty two off 50 top hundred and and you can see what's going on here
Hey, so just for fun. This is adventureworks. It's kind of hard to find
You know nuggets of wisdom or insight in here, but we're gonna expand it to more maybe
Thousand ten thousand customers and just see what it looks like
Alright, so just hit edit queries and this popped up or query editor and in here
I'm gonna go back to the top and query go back to the source that I'd entered and I can now say something like
500
Thousand let's say ten thousand. Let's stop there hit okay hit close and apply and now
I expect this table to expand and we can see some of the other analysis
So how have we been doing? If you look at our top 500 customers boy, so this is really interesting
you see this big dip in June and and but generally there's a downward trend so generally our
400 customers the percentage of revenue they
Contributed went all the way from nearly hundred percent down to sixty percent
So clearly we expanded our customer base
And well ten thousand is probably going to be a flat line because that's nearly all for customers. But this is interesting
so again
You can see that something happened in June
and if you have been following it when she works closely my friend have been paying attention to the
To the stock market news then June was a big month for them and there was a lot of activity there
So there's clearly some correlation here
So I'm just messing around having fun with adventureworks as much fun as possible with adventureworks
Now of course real data is where the real fun happens
So apply this to your real data set and see what kind of insights you can find the top end
analysis is one of my favorite analysis to
Understand how a business is doing and how top-heavy is it how has that changed over time?
And where is that right line between serving your top customers?
but also expanding your customer base as promised you can access the complete top and playlist on the
Screen by clicking on the cards or in the description below now more videos will be added to it
So make sure you stay subscribed
Until next time bar on my friend. Hey keep watching more videos and deep 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