Display Different Top n from a Slicer - EASY Trick Works in Excel or Power BI
Ғылым және технология
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
Пікірлер: 116
Once again a great video. Your channel is an excellent source for knowledge.
Every one a winner, Mynda. Loved this one. Thank you.
@MyOnlineTrainingHub
2 жыл бұрын
:-) great to hear, Steve!
Awesome Mynda! Thanks for the great lesson and also for providing the sample file to follow along.. very helpful and appreciated! Thumbs up!
@MyOnlineTrainingHub
2 жыл бұрын
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.
@MyOnlineTrainingHub
2 жыл бұрын
So pleased to hear that 🙏
Always great Mynda thank you again - and for making downloads so easy makes us come back over and over 🙏
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it, Alex!
Great video Mynda. Thanks for these doses of DAX.
@MyOnlineTrainingHub
2 жыл бұрын
My pleasure!, Ivan!
Thank you - your tutorial is saving my life!
@MyOnlineTrainingHub
2 жыл бұрын
You are so welcome!
Your work is so, so beautiful! It helps me a lot! Greetings from Brazil!
@MyOnlineTrainingHub
2 жыл бұрын
Happy to hear that!
Great! Your videos are really so helpful for my work. Thanks a lot!
@MyOnlineTrainingHub
2 жыл бұрын
Glad you like them!
Fantastic presentation of these topics. Thank you
@MyOnlineTrainingHub
2 жыл бұрын
Glad you enjoyed it!
Clever combination of measures :-) Thks for your clear explanations
@MyOnlineTrainingHub
2 жыл бұрын
Glad you like them, Joseph!
Thank you for teaching!
@MyOnlineTrainingHub
2 жыл бұрын
My pleasure!
Hi Mynda!Super Helpful Tutorial..Thank You :)
@MyOnlineTrainingHub
2 жыл бұрын
Great to hear, Darryl!
thank you again. your tricks are outstanding.
@MyOnlineTrainingHub
2 жыл бұрын
Thanks so much, Faysal!
Thank you. From Russia with Love
@MyOnlineTrainingHub
2 жыл бұрын
Thanks so much!
It's very useful, Mynda... Thx for sharing with us something so good and practice! Keep it up... It's amazing... Thx again!
@MyOnlineTrainingHub
2 жыл бұрын
Thank you! Will do!
Brilliant!!! Grazie. Merry Christmas to everyone 🥂🐞
@MyOnlineTrainingHub
7 ай бұрын
Same to you!
Very useful, as usual!!Thank you
@MyOnlineTrainingHub
2 жыл бұрын
Glad to hear that, David!
That is a great vid with a really cool solution. Thank you Mynda
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it, Rory!
Another great video...ty for the awesome content and impeccable delivery...as always
@MyOnlineTrainingHub
2 жыл бұрын
Thanks so much, Larry!
Thanks Mynda!
@MyOnlineTrainingHub
2 жыл бұрын
Cheers, Chris!
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! 😊👍
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it 😊
@HooterCooter
2 жыл бұрын
@@MyOnlineTrainingHub How would we conduct this same exercise, but instead of "Top 5" we have the percentile such as "Top 90%"
Thanks love from India :)
@MyOnlineTrainingHub
2 жыл бұрын
My pleasure :-)
Great Stuff, thank you!
@MyOnlineTrainingHub
2 жыл бұрын
Cheers, Nick!
Really helpful
@MyOnlineTrainingHub
2 жыл бұрын
Glad to hear that :-)
Thanks for sharing
@MyOnlineTrainingHub
2 жыл бұрын
My pleasure :-)
Awesome!
@MyOnlineTrainingHub
2 жыл бұрын
Glad you think so!
Beautifull!!
@MyOnlineTrainingHub
2 жыл бұрын
Thank you! 😊
Very nice.
@MyOnlineTrainingHub
2 жыл бұрын
Thank you, Drew!
Very useful
@MyOnlineTrainingHub
2 жыл бұрын
Glad you think so!
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.
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it, Matt!
Great!👍👍👍
@MyOnlineTrainingHub
2 жыл бұрын
Thanks! 😃
This is cool 👍
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it, Anil!
awesome
@MyOnlineTrainingHub
2 жыл бұрын
Thank you!
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.
@MyOnlineTrainingHub
2 жыл бұрын
Correct, that will work.
I like learning this and it will be quite helpful but man some things in Excel.... Thank you MOTH
@MyOnlineTrainingHub
2 жыл бұрын
😊 great to hear, David!
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.
@MyOnlineTrainingHub
2 жыл бұрын
Glad it was helpful, Perry! All the best with two top N slicers. I haven't tried this myself.
It's great 👍, do you have a free course for power pivot?
@MyOnlineTrainingHub
2 жыл бұрын
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.
@MyOnlineTrainingHub
2 жыл бұрын
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.
@barttitulaerexcelbart9400
2 жыл бұрын
@@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...😉
@MyOnlineTrainingHub
2 жыл бұрын
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 ?
@MyOnlineTrainingHub
2 жыл бұрын
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.
@jpandy6464
2 жыл бұрын
@@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
Hi, how do you apply the filter to select "1" to the "include sub-category" column/measure in PowerBi? thank you
@luisalonso3018
2 жыл бұрын
Got it. In the Filters Pane... "show items when the value is 1". Thank you for your great videos!
@MyOnlineTrainingHub
2 жыл бұрын
Add 1 to the Display Top table.
What if I have Location, Major Category, Category Sub Category? Do I need to add Measures?
what if i need to add all remaining sub category as others after applying top N
Mam can you explain final account..
Can you do something like this 2013 excel versions since it doesn’t have powerpivot
@MyOnlineTrainingHub
2 жыл бұрын
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.
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
@MyOnlineTrainingHub
2 жыл бұрын
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: kzread.info/dash/bejne/fmh217ezk8ayoNI.html
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?
@MyOnlineTrainingHub
2 жыл бұрын
Hi Steven, instead of the RANK formula, you can calculate % of Budget and use that field instead.
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...
@MyOnlineTrainingHub
8 ай бұрын
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!)
@MyOnlineTrainingHub
2 жыл бұрын
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?
@MyOnlineTrainingHub
2 жыл бұрын
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...
@MyOnlineTrainingHub
2 жыл бұрын
Oops, sorry about that. I've fixed the link now.
The sample Excel file is not available
@MyOnlineTrainingHub
2 жыл бұрын
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.
@MyOnlineTrainingHub
2 жыл бұрын
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
@MyOnlineTrainingHub
2 жыл бұрын
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.
@MyOnlineTrainingHub
2 жыл бұрын
Correct it's designed to show the top n based on numbers.
@krischlapek6939
2 жыл бұрын
@@MyOnlineTrainingHub would you mind making another tutorial based on text please? Aka top 5 components of machinery? Thanks
Thanks for sharing
@MyOnlineTrainingHub
2 жыл бұрын
Thanks for watching!
@tobayekaina8610
2 жыл бұрын
Can't access the sample Excel file