Let me show you how you can create a Top N
report in Power BI with a variable n so that you can show the numbers and
the detail for top ten top ot top hundred customers or
Products or anything else and still have it fully
Interactive so you can slice and dice it any which way you want.
This is part of our Top N playlist where we would be covering a lot of different topics
around the Top N theme you would be able to access the complete playlist at the end of this video.
So let me recap the content. We have covered so far in previous Top N videos.
We figured out how we can apply a top ten filter to see the details
for example the top 10
customers and then we also figured out how we can use that in a Top N function to get the
numbers about our top 10 customers
so the sales coming from them the percentage sales and so forth see the trend and
in the next step
We took this the top and function and made that variable so we could see the numbers
Coming in from our top 10 customers, but then change that to see hey
What's the contribution for it's off 50 or top hundred customers? All right. There we go
So what we're trying to do next is take this approach where we're able to weary the top ten
fifty hundred five twenty-five, whatever you want and
Be able to apply that to this filter. So let me set up that sheet and we'll take it up from there
so I have combined the two different elements as
We have the the measures using the top and function which shows us the number
so top
10 customers are contributing ninety-eight thousand six hundred four dollars in our sales that is 0.6 percent off or overall sales
So it has a number and in the previous video we have made that to be dynamic so we can change that to twenty
Fifty hundred or whatever else we want now I have brought in the second element and this is not dynamic
so this is our goal for this video to take this mist of
Detail showing the customers and right now you can see that I'm showing top end customers
But have I searched 20 the rest of the report the top-end portion changes, but this one still stays at top 10
so we're gonna gonna change this and by the way,
As always any of the files shown in my video
You can just go to learn power bi comm slash download and get the file so you can follow along
Examine it in more detail
So if we look at this and if you remember how we had filtered initially it's by using the top 10
filter over here
So for the first thing we're gonna do is we're gonna remove that filter because that's static and there's no way that we can change
that to be dynamic
I mean
I wish I could drop in a measure over there and then we would just go to our
selected any measure and just put that in there selected n just gives me return 5 10 15 20
Whatever selected but it's it's not doesn't doesn't let me do that. In fact, I tried to add that as a filter
alright, so well, so we're gonna clear this filter because
To kind of get started. So now we're seeing all customers. Now. How do we make this dynamic now interestingly
Top n has a cousin which is rank X and top and then rank X often, you know kind of work in tandem
So that's our key
so we're gonna define a rank measure customer rank measure and just follow along watch me step by step as a how
Has happened to see how I do it. So let's go to modeling. I'm gonna say new measure. Alright, and we have
Let's say customer
Rank, and this is the standard the rank X function and the only catch with rank X's you might be tempted to say
Customer but that if you can try it out, it wouldn't quite work the way you expect and you have to say all customer
I wouldn't go into the detail in this video as to why that's the case
so rank all customers by sales and that is our customer rank function and
Looks good
And we're gonna come back and move this
Funk move this new one to a formulas v3 since this is the part 3 of the video and we can add this
Over here. So you see where we're getting somewhere with this not only if you could tie
You know tied this number customer rank to respect this one now again
Even if we go look at the customer rank filter
There's no way to tie it to a dynamic measure
We can't say the customer rank is selected than a measure
So we can't quite do that and hand it added that as a filter. So let me remove that
So what we're going to do is take another
step and add
one more column
Measure actually which compares the customer rank would select a 10 so let's do that
so we're gonna come back to formulas we tree and we say new measure and we'll say is
the customer rank
within
Selected in less less than selected an album. Yeah, we'll go with that and we'll just say
mm
customer rank this term or
rank is
less than equal to
Selected in
And that is our measure. Let's see how it performs. Let's add it on there so you can see
Top 20. Let's try top 10. So for top 10, you can see that it is it is
So I selected n is 10 so up to 1 to 10. It is true and after that it is false
So this is good. This is really promising. And of course if you change that to let's say top 5
perfect so we can see top 1 through 5 ranks are true and
And then it's false. So now
We don't have to filter you don't you're not trying to do a dynamic filter
We can just say whenever
This value is true and that value since that depends on the selected and is going to be dynamic, right?
I mean you saw it's going to be
5 values gonna be true and then 10 value is going to be true
and of course this is gonna be top 50 and so forth so so although
when you go into the customer rank and try to set a
Customer rank, the new field that we defined customer rank within selected n it actually doesn't let you do it
It's really weird. And I think that's a bug and I filed that with the Microsoft power bi team. Hopefully it really thick soon
so we're gonna just change that a little bit and
we're gonna make
instead of a
True/false datatype we're gonna make this an integer because somehow it seems to false
It doesn't let me choose a filter other filter. So we'll say if customer rank
Is this then one true, you know or else Sara or we could have said blank doesn't really matter
And now if we go back to that field again now we have that filter available to us
So let's go over here. And we're gonna say naught is less than will say is
one oops
Let's try again
Is
One and we're gonna apply that filter are you ready my friends
Whoo-hoo. So now we're only seeing the top ten
Oh, there's a slight hiccup here and we'll tackle that later, which there are two Jordan Turner's here
We're gonna cover that in one of the follow-up off this top and series for now
We're gonna just bring in customer key to avoid that scenario. So that is our final solution
Let's just check to make sure it works. So we have top ten. It's only showing the top ten and
now it's showing the top five and I can change that to top twenty or top fifteen or top fifty or top hundred and it
Still kind of shows me
Those number of customers now, of course. I don't need any of these fields in there
I don't even need kind of customer count customer and customer that I can just remove all of that
It'll still be in the filter pane so I can remove it from the display
So I'm gonna remove kind of customer count customer rank and customer. Yeah, you know the last one that we had which
Compared it to selected n and it would still work. Actually. You know what I'll come back. I'll keep customer account that just
Helps me kind of see that it's working fine and again
So it's not you don't have to show that value but it is in the filters for that
So and that's what's making it work that it's checking
This value is customer rank within the selected and you can get this file by going to learn power bi comm slash download
Let me know what you thought about this video and the approach that I showed you today
Let me know what you thought about the top and series so far you can access the complete playlist by clicking on
On the cards or down in the description below until next time balrog
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