Display Different Top n from a Slicer - EASY Trick Works in Excel or Power BI
Vložit
- čas přidán 30. 07. 2024
- Toggle through different top n values using a Slicer with a PivotTable, Pivot Chart or Power BI. Download the sample Excel and Power BI files here: www.myonlinetraininghub.com/t...
Learn Power Pivot with Mynda's course: www.myonlinetraininghub.com/p...
0:00 Introduction
0:18 The set up
1:00 Measures
2:58 Create PivotTable
4:14 Insert Slicer
4:42 Insert Pivot Chart
5:52 Dynamic Chart Title
Connect with Mynda on LinkedIn: / myndatreacy - Věda a technologie
Awesome Mynda! Thanks for the great lesson and also for providing the sample file to follow along.. very helpful and appreciated! Thumbs up!
Glad you enjoyed it, Wayne!
You and your Excel Videos are truly wonderful. I have learned more practical Excel from following along in your videos than I have in any online or self study course. Bless you.
So pleased to hear that 🙏
Every one a winner, Mynda. Loved this one. Thank you.
:-) great to hear, Steve!
Always great Mynda thank you again - and for making downloads so easy makes us come back over and over 🙏
Glad you liked it, Alex!
Once again a great video. Your channel is an excellent source for knowledge.
Thank you!
Your work is so, so beautiful! It helps me a lot! Greetings from Brazil!
Happy to hear that!
It's very useful, Mynda...
Thx for sharing with us something so good and practice!
Keep it up... It's amazing... Thx again!
Thank you! Will do!
Great! Your videos are really so helpful for my work. Thanks a lot!
Glad you like them!
Thank you - your tutorial is saving my life!
You are so welcome!
Great video Mynda. Thanks for these doses of DAX.
My pleasure!, Ivan!
I somehow missed this video before, but glad I found and watched it! Thanks Mynda for a very useful and innovative technique which sure is going to help in many scenarios! 😊👍
Glad you liked it 😊
@@MyOnlineTrainingHub How would we conduct this same exercise, but instead of "Top 5" we have the percentile such as "Top 90%"
Clever combination of measures :-) Thks for your clear explanations
Glad you like them, Joseph!
That is a great vid with a really cool solution. Thank you Mynda
Glad you liked it, Rory!
Another great video...ty for the awesome content and impeccable delivery...as always
Thanks so much, Larry!
Fantastic presentation of these topics. Thank you
Glad you enjoyed it!
Brilliant!!! Grazie. Merry Christmas to everyone 🥂🐞
Same to you!
Hi Mynda!Super Helpful Tutorial..Thank You :)
Great to hear, Darryl!
Thank you. From Russia with Love
Thanks so much!
thank you again. your tricks are outstanding.
Thanks so much, Faysal!
Very useful, as usual!!Thank you
Glad to hear that, David!
Thank you for teaching!
My pleasure!
Thanks Mynda!
Cheers, Chris!
Great Stuff, thank you!
Cheers, Nick!
Thanks love from India :)
My pleasure :-)
Thank you Mynda! This is a great example to use measures. It would be great if you can you please confirm a way to see the Top N subcategories by adding one (or more slicer). eg. If I want to show Top N subcategories (here it is disconnected model) and also have another slicer to select Main Category (which may be connected model) to see the top N subcategories within the main category rather than complete data.
Correct, that will work.
Thanks Mynda, there was a lot of good info in this one. If not for the download file, maybe too much in such a brief video. Nonetheless, great info.
Glad you liked it, Matt!
Awesome!
Glad you think so!
Beautifull!!
Thank you! 😊
Really helpful
Glad to hear that :-)
I like learning this and it will be quite helpful but man some things in Excel....
Thank you MOTH
😊 great to hear, David!
Great!👍👍👍
Thanks! 😃
Thanks for sharing
My pleasure :-)
This is cool 👍
Glad you liked it, Anil!
Very nice.
Thank you, Drew!
Hi Mynda, I feel like I have turned a corner watching this, thanks so much! What I am now trying to understand is if it is possible to have an additional Top N series within one pivot table. For example, I want to be able to have 2 slicers in one pivot table as follows 1. The number of cars sold to a customer 2. The value of cars sold to a customer and be able to click in-between these.
Glad it was helpful, Perry! All the best with two top N slicers. I haven't tried this myself.
Very useful
Glad you think so!
awesome
Thank you!
It's great 👍, do you have a free course for power pivot?
Thank you! Not a free course, but a comprehensive paid course here: www.myonlinetraininghub.com/power-pivot-course
Thank you Mynda. One question: why did not you put the value of the slicer in the "filter" part of the pivot table? (they used to call this the page field earlier). Or is that because the data is in the power pivot? In regular pivot it should work as well....? thanks.
Hi Bart, you can put it in the PivotTable if you want, however it's not necessary to have the field present to be able to filter using the Slicer.
@@MyOnlineTrainingHub Hi Mynda. But why at all did you make a copy of the pivot table then? It would be easier to add the field to the existing pivot in the filter area. Of course not the measure but the field name. btw I found a solution to harvest the value of a slicer of a table....I will make a video soon...😉
Because when you put the field in the filter area it only shows you the item name if only one item is selected. If multiple items are selected you cannot see the names of those items.
Thank you .It is very useful. But once the top N filter effects be cleared by another slicer , I have to repeat the step 3:50 right ?
The Top N filter will default to top 5 if no items are selected in the Top N slicer, whether that's because another slicer filtered the table or not.
@@MyOnlineTrainingHub
Thank you for your reply.^^
For example , sometimes I want to show the Top N items by the "Top N slicer", and sometimes I want to show the particular items just by "sub-category slicer" to filter the same pivot table.(2 slicer control the same pivot table)
Once I use the sub-category slicer to filter pivot table and than “clear filter” to reset it, the Top N slicer doesn’t work ...
How to display all values if we unchecked the filter? In the default settings it shows the top 5
what if i need to add all remaining sub category as others after applying top N
Hi Mynda, How would we conduct this same exercise, except instead of Ranking the Top 5, we want to show what subcategories are within 90% of our budget, or 80% of our budget?
Hi Steven, instead of the RANK formula, you can calculate % of Budget and use that field instead.
If you need to update the data how do you do? or you want to make sure that it takes the updated data source? Thanks
You use Power Query to connect to your data source, then you can just click the Refresh All button on the Data tab to update your reports: czcams.com/video/L4BuUzccLpo/video.html
Mam can you explain final account..
Can you do something like this 2013 excel versions since it doesn’t have powerpivot
Standalone versions of Excel 2013 do have Power Pivot and Office 2013 Professional Plus. If you don't have Power Pivot, then you can't do this trick with Slicers.
What if I have Location, Major Category, Category Sub Category? Do I need to add Measures?
Hi, how do you apply the filter to select "1" to the "include sub-category" column/measure in PowerBi? thank you
Got it. In the Filters Pane... "show items when the value is 1". Thank you for your great videos!
Add 1 to the Display Top table.
Hi mam if drag partner name into the pivot table and Sub category in the rows sections and total amount in values section. When I select Top 10 it is showing Top 10 by each partner and and partner name is sorted by default a to z .. in the case I am missing partners with most used amount. .. how can get top 10 partners with sub category also...
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Link to download sample file not working (for me anyway!)
Oops, sorry about that. I've fixed the link now.
H, My Pivot table field list has been locked ? Can you let me know why it got locked and also on how to unlock it?
Not sure what you mean by 'locked'. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
The sample Excel file is not available...
Oops, sorry about that. I've fixed the link now.
The sample Excel file is not available
Oops, sorry about that. I've fixed the link now.
I had this challenge where I have a date slicers and I have partners data where each partner will sign 1 year agreement. My criteria is I have a date slicer if I select any 1 month I need a chart for the renewal of those partners who expired in the selected month. I want counts by months when they are renewing.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
It is impossible to download the file
Oops, sorry about that. I've fixed the link now.
This function isn`t going to work if instead of numbers in the "Amount" column you add text.
Correct it's designed to show the top n based on numbers.
@@MyOnlineTrainingHub would you mind making another tutorial based on text please? Aka top 5 components of machinery? Thanks
Thanks for sharing
Thanks for watching!
Can't access the sample Excel file