How to Create Power BI TOP N Report (Power BI Top 10 / Top N Filter)
Vložit
- čas přidán 30. 10. 2018
- 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 this with another approach using the DAX TOPN Function.
👉 Download Power BI File in Video at web.learnpowerbi.com/download/
This is part of our complete TOP N playlist where we would be covering lots of different topics around the TOP N theme.
➔ Links mentioned in the Video
• Power BI Top N Series Playlist: • How to Create Power BI...
👉 FREE Power BI Step-by-Step Tutorial web.learnpowerbi.com/tutorial
👉 Download Accompanying PBIX Files for Video at web.learnpowerbi.com/download/
✅ Subscribe to always get my latest videos: goo.gl/P79Hcr
🔴 Join me Fridays for Talk Power BI LIVE czcams.com/users/PowerBIProlive
👪 Join our Talk Power BI Insider's Club www.TalkPowerBI.com
===Most Popular Playlists===
• Power BI Beginner Tutorial: goo.gl/KRRkPG
• Ultimate Calendar: goo.gl/pyki4K
• Power BI Data Modeling and more: goo.gl/WNtJFv
⚡Power On!⚡
-Avi Singh
www.LearnPowerBI.com
www.AviSing.com - Věda a technologie
*What New Videos should I create in the TOPN Series?* Let me know which ones you like or add your own by leaving a comment
• Variable N in Top N: User can control whether to show Top 5, Top 10, Top 20, Top 100...etc. Dynamically
• Show Ranking of Customers this Year and Prior Year and show who dropped and who gained spots (how they show the Top 10 Music Charts, who climbed from #3 to #1 and who dropped from #2 to #5...)
• Show the Top 10 Customers by Year in a Matrix (with Year as Columns): This was asked by Lorena in the my PowerBI Session at the PBI World Tour
• More Control in Showing the Top 10 Customers (by Filtering using a Measure). Break Ties etc. We'd create a RANKX Measure and use that to filter (this would solve "Darkhan"'s question below of showing the Top 5 and Bottom 5 Together
• Others...just leave a comment below
I would like to see how you know to use TopN vs RankX functions.
Yes, those two are like close cousins. I have a feeling I would end up covering both in the series. In fact Part 3 in the series does use RANKX for a specific solution. See video linked below
czcams.com/video/wKfPS1yZfE0/video.html
⚡Power On!⚡ / Avi
★ Power BI Beginner Tutorial: czcams.com/play/PL7GQQXV5Z8ef2SjkDpLnvsz7TAQjlzlpO.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
Great video! Thank you! Could you show how to create a top n and other (fixed n) with direct query on an SSAS tabular cube data source? So to list maybe the top 10 products by sales amount and then Others total in a grid. Thank you!
can u share the DAX which u used for TOPN?
Hi Avi, Can you create videos on TOP N with Filter conditions like (Top N based order priority and status etc)
Just curious, is there a way to have anything not captured in the top N filter to be dynamically populated into an "Other" catchall category. I know you can use DAX if statements in the data tables, but that won't help as I am trying to have the top N be dynamic, following any filters, slicers or drill-throughs that the user has enabled.
how would you use the Top N with the Treemap visual but i want to Count the number of occurrences?
I decided to try my hand on Power BI and stumbled upon your channel. Loved the energy and your explanation. Thanks. Have subscribed to your channel. Keep the videos coming in
Hey i didnt rcv any pbix finished file.....i followed the prompts but ...its asking for registration of some webinar....?????
I have one-month of sales but I want 10 days of sales in this month using DAX Functions. can you create new columns?
Hi Avi, what is your customer key that is being used as the filter? Thanks
Hey Avi - How can we resolve the ties on Top N or Bottom N. Please let me know if there is a way to do it.
Just started learning PBI 1 of 3 or so in the entire company... I really wanted to be 'AWESOME' at this... thank you for helping me try to figure out why I couldn't turn on the 'apply filter' I didn't now about the 'value' part... or really anything... just been playing with stuff I currently have set up in a database (data from 3 locations linked) that in turn goes back out to Excel for my users.. this is going to be awesome! I can put it all right here... our IT department is currently try to figure out how to connect PBI to my Oracle & Access database... everyone is very 'green' on all of it... I hope they figure it out soon. right now just pulling all the data from Excel into PBI... I subscribed, and scheduled for something tomorrow, and click the bell for live notifications too.... I am determined to be the best at it... THANK YOU.....
Thanks for the inspiring feedback. I really appreciate it.
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
what about showing data as top 3 and the rest of the data in' others' bucket
hi there. how did you move Filter field under Vizualization field?
Hi Sir, I need help with a scenario. Its an interview question. I have to display the top 1 customer based on orders column. It should be done using dax.
Can I also have last year sales amount along with the shown current year sales amount for the same top customers in the present year
Can we get the top 5 and bottom 5 in same charts by any function? (Without creating any additional table/ column). If yes, can you pls tell how to do
Awesome video,
However, I saw there was a customer with 11000$ in sales but when you filter for the top 10 that customer does not show up.
Is there anything I'm missing here?
Thanks
Hi Bro, Could you please help me on this one:
I have a list of customer and sales, I want to have a total sales of each customer (group by) and then order it DESC, the top sales customers are on top, then I want to select top dynamically (variable %X) of total revenue and power bi list in the table only Customers who contribute top X% of sales.
Thank you so much.
Thank you for your tutorial, it is really helpful. However, I tried to download your file but fail, please advise.
Is there a way to create an 'all other' bucket? This would allow my audience to easily see how much the top 10 would represent of the grand total.
Hey Avi. I tried to download the pbix but not receiving any thing
Hi Avi. You mentioned a dax formula to create a "Sales Top 10 Customer" but you never showed the formula. I can't recreate it or see it in your downloads.
Avi... If you're sharing information, it should be free... Half/ hided information is always lethal...
It's as good as wasting our time
@@adityakalra7383 exactly
Hello, I just found this video which would be really helpful if I were able to see the DAX formula to calculate top 10 customer sales. Can you share the formula? Thanks!
@@camilabelassi6566 Sales Top 10 Customers = CALCULATE([Sales], TOPN(10, Customer, [Sales], DESC, Customer[CustomerKey]))
Hi, I created the top 10 customer filters and top 10 customer measures. bt don't know why coming different value. I cant understand. I did everything step by step.
I tested the option TopN filter in page or report level. It doen´t work. You can not set a measure als page or report filter. And, for the other columns you can not set them as TOPN filter, only as basic or advanced filter.
Can you help me with the link of the video, that you have created for top n with dax function
Hey Smriti, We have a number of top N videos. Here is the Link-->czcams.com/users/PowerBIProsearch?query=top%20n%20with%20dax%20function
^RM
Bless your Power BI yellow heart, Avi.
Have a question with Top N filter. I can make it work using Filter Top N by values in Power BI and display the contents in a table. Now I need to add a new column of the N values in the same table and display them in the same table. Can you help? Thanks.
Hey, I think I need to better understand your question. Could you message me on LinkedIn with some more details (like perhaps a screenshot of what you are trying to do...may be you can show me in Excel what you're trying to achieve). Here's my LinkedIn Profile where you can message me: www.linkedin.com/in/PowerBIPro
Power On! / Avi
That's a great form to explain a topic! Thumbs up (y) for you Avi Singh! On my dashboard (simple one) i'm using the Advance filter TopN thanks a lot!
Hi Bto Zapata Elizondo.
Nice! You are welcome. It's a pleasure knowing that this video helped you.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
Hi Avi, @10.17min of the video, How do we achieve a List of Top10 Customers (using the same Visual Filter) Table / Matrix visual with the total of $25.293 instead of $27,807. Thanks in advance.
please while explaining visuals, give some idea of formulas you're using such as countrow or distinct count
Thank you so much! Short video, but it's very helpful!
please show the "sales top 10 customers" formula
What will be the measure for the sales of top 10 customers ?
Thank you! Very useful, will use it.
Can we show in one table Top-5 and buttom-5 together?
Darkhan, Glad you liked it.
Top 5 and Bottom 5 would be possible in the same able. But likely you would need to create a RANK Measure and use that to Filter the Visual.
Just curious - what is your scenario? Why do you want to show Top 5 & Bottom 5 together? You could easily show them in two tables. Side-by-Side or one above the other. Just curious as to the need to show them in the same table. Join me for my next Live and may be we can discuss it further.
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
@@PowerBIPro thank you for the answer. I can send you an example of my chart in excel. Can you please have a look and tell how i can do it in power bi. May be it will be also useful for others.
@@user-ll2sn8kp8w would absolutely love to help. But the best forum would be my TalkPowerBI Live call on Fridays. Would you be able to join this weekend? (link below)
Talk Power BI Fridays czcams.com/users/PowerBIProlive
The other option would be, if you were inside my 'Learn Power BI' paid program you could get more direct help from me and other amazing students :-)
Enrollment opens Tuesday November 13 with the 5-Day Power BI Challenge (will share more on that as the date gets closer).
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
@@user-ll2sn8kp8w Did he help you? I have the same doubt
How do you display the DAX code on the top side of Power BI
When you click on a Measure (on the right side), the edit window will pop up on top of the report, you can then click on the chevron symbol (V)
and a bigger edit window will show. remember to format your DAX to be readable.
Thanks sir..
I tried to apply top 10 filter on my visual,
But the field which I'm using in top N is having same value multiple time
So sometimes my visual gives me top 11 or sometimes top 13
For ex.
Parameter values
A 9
B 8
C 7
D 6
E 5
F 5
G 5
H 4
I 3
J 2
K 1
L 0
and if i apply top 5 filter,
Then it shows top 8 parameters as value 5 is repeating.
Is there any way to get exact top 5?
I hope I'm able to explain you my problem.
Thanks in advance
Hi Avi, I tried to download the file through your page, but does not arrive to my e-mail any answer with the key.
Is there something wrong?
Thanks a lot and keep on doing so good videos about PBI!
Josep
Josep, sorry you're experiencing difficulties with the downloads. My team would check to see the downloads are working.
Meanwhile if you like, you can send an email to avi@avising.com and we can send you the file for this video directly. Do mention the URL of this video in the email you send.
⚡Power On!⚡Avi
** Join me for next #TalkPowerBI Live on Fridays www.CZcams.com/PowerBIPro/Live **
Hi Josep, we tested the downloads page and it seems to be working fine on our end. Let's try it again, Josep. Please re-enter your details here web.learnpowerbi.com/download/ and follow the prompts. Let me know if you are able to access the files. If you are still having trouble in downloading the files, feel free to send me an email at avi@avising.com and I'll be glad to assist you further. - Power On! - Marianne / Team Avi
What is the equivalent of Index() function from Tableau to Power BI?
It took me a while to figure this out, but it only works on the visual filter…not the page or report filter. I missed that in the video.
Thank you. I was stuck because of this issue as well.
Thank u so much for this one
Thankyou So much Avi ji, You saved me today :P
Avi,, sorry file not found
Hi A V Singh....very nice.
I think so Create DAX function is better way.
Guda Krishna, glad you enjoyed the video. Yes - I love the Measure approach. It's really powerful.
But I realized that if you actually want to list the Top 10 Customers (show their names), then Filter would work better.
You can also do that using a Measure, but it would be a measure using RANKX (not TOPN). I may try that for a future video.
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
Avi Singh - PowerBIPro waiting for your next video
where did the customer key came from??
Hi Camille Gonza.
Hmmm...the Customer Key was part of the data. You can create one yourself in Query Editor if you need by adding an Index column.
If you're still stuck ask me in my next TalkPowerBI. I go live each Friday. Link below.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
Very cool, but is there a way to actually only show the top 10 in the table as well?
Yes! I thought about that when recording the video. I think it certainly can be done by creating a Rank measure (using RANKX). But RANKX Only takes a single Order By Expression (unlike TOPN, which can take multiple, to help break ties). For ties the only option RANKX has is to use Skip or Dense Ranking (which actually only impacts the Ranking for the Item AFTER the ties, ties still show as the same Rank). So be sure to expect a follow up video covering that :-)
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
i need the query in dax
Great Video easy to follow!
Hi Michael Doyle.
Thanks for the compliment mate!
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
thanks ! your video helped me a lot!
How to get top ten records of three years , & how to compare in a single visual
Thanks mate very useful
Very nice video and Helpful🙂
Avi you're awesome! Thanks for each video I'm learning fast
Thank You Sam! Yes...keep it up! It's amazing what you can accomplish with Power BI in a relatively short amount of time. Join me on my Friday LIVEs if you get stuck or need additional help.
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
Great video Singh. Keep up the good work
Thanks for your kind words Clarkson. Really appreciate it.
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
topn dax function would be better
Hi Dinesh.Yup! I agree...I think using the function is a lot more powerful and flexible.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talk ★
You are hiding data in your presentation... Values of Countries by top 10 customer we not shown...
where is the file . avi this is cheating . you share half info !
Hey Isha,
You can download the tutorial files here: web.learnpowerbi.com/download/
If you are having trouble in downloading it, kindly send us an email at avi@avising.com and my team and I will assist you .
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
Why don't you just show the DAX used in this video, make it simple.
Not displaying the actual DAX measure while I spend 10 minutes on this video..
Vassileios, sorry it seems like this video was not the right fit for you. I was trying my best to explain the concept. If you do find a more compact video which shows the DAX Measure, then please share it here so it can help others.
Meanwhile make sure to check out the Calendar Table: czcams.com/video/zXZAZrUwUe8/video.html
A lot of users find it very helpful.
And of course, if you are ever stuck with a DAX, Power Query/M or Visualization problem.
See here on how to get help and get your questions answered: www.linkedin.com/pulse/how-ask-good-power-bi-questions-get-them-answered-singh-powerbipro-/
Power On! / Avi
your lecture is movie without a climax, you have showed everything but not the main part of top n dax function, waste of time
Having you on the screen is super distracting