Display Different Top n from a Slicer - EASY Trick Works in Excel or Power BI

[@kentlindgrempyml704]

Once again a great video. Your channel is an excellent source for knowledge.

[@MyOnlineTrainingHub]

Thank you!

[@COURSSTATSCHAMBERY]

Clever combination of measures :-) Thks for your clear explanations

[@MyOnlineTrainingHub]

Glad you like them, Joseph!

[@wayneedmondson1065]

Awesome Mynda! Thanks for the great lesson and also for providing the sample file to follow along.. very helpful and appreciated! Thumbs up!

[@MyOnlineTrainingHub]

Glad you enjoyed it, Wayne!

[@mhalsey]

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]

So pleased to hear that 🙏

[@stevet3331]

Every one a winner, Mynda. Loved this one. Thank you.

[@MyOnlineTrainingHub]

:-) great to hear, Steve!

[@herisoaandriambeloson8229]

Thank you - your tutorial is saving my life!

[@MyOnlineTrainingHub]

You are so welcome!

[@giampaolocorradi1783]

Brilliant!!! Grazie. Merry Christmas to everyone 🥂🐞

[@MyOnlineTrainingHub]

Same to you!

[@al3xj]

Always great Mynda thank you again - and for making downloads so easy makes us come back over and over 🙏

[@MyOnlineTrainingHub]

Glad you liked it, Alex!

[@kuatle]

Thank you. From Russia with Love

[@MyOnlineTrainingHub]

Thanks so much!

[@marcusvfo]

Your work is so, so beautiful! It helps me a lot! Greetings from Brazil!

[@MyOnlineTrainingHub]

Happy to hear that!

[@andrewmattinson5344]

Fantastic presentation of these topics. Thank you

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@vijayarjunwadkar]

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]

Glad you liked it 😊

[@HooterCooter]

@@MyOnlineTrainingHub How would we conduct this same exercise, but instead of "Top 5" we have the percentile such as "Top 90%"

[@FaysalEasyExcel]

thank you again. your tricks are outstanding.

[@MyOnlineTrainingHub]

Thanks so much, Faysal!

[@rory1653]

That is a great vid with a really cool solution. Thank you Mynda

[@MyOnlineTrainingHub]

Glad you liked it, Rory!

[@AprendaWell]

It's very useful, Mynda... Thx for sharing with us something so good and practice! Keep it up... It's amazing... Thx again!

[@MyOnlineTrainingHub]

Thank you! Will do!

[@IvanCortinas_ES]

Great video Mynda. Thanks for these doses of DAX.

[@MyOnlineTrainingHub]

My pleasure!, Ivan!

[@lchase7858]

Another great video...ty for the awesome content and impeccable delivery...as always

[@MyOnlineTrainingHub]

Thanks so much, Larry!

[@user-dn1kb6cv3g]

Thank you for teaching!

[@MyOnlineTrainingHub]

My pleasure!

[@davidrhodes6016]

I like learning this and it will be quite helpful but man some things in Excel.... Thank you MOTH

[@MyOnlineTrainingHub]

😊 great to hear, David!

[@DK_85]

Great! Your videos are really so helpful for my work. Thanks a lot!

[@MyOnlineTrainingHub]

Glad you like them!

[@mattschoular8844]

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]

Glad you liked it, Matt!

[@perryharrison5696]

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]

Glad it was helpful, Perry! All the best with two top N slicers. I haven't tried this myself.

[@SD-ko4jd]

Thanks love from India :)

[@MyOnlineTrainingHub]

My pleasure :-)

[@SumitSingh-fx1wp]

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]

Correct, that will work.

[@darrylmorgan]

Hi Mynda!Super Helpful Tutorial..Thank You :)

[@MyOnlineTrainingHub]

Great to hear, Darryl!

[@chrism9037]

Thanks Mynda!

[@MyOnlineTrainingHub]

Cheers, Chris!

[@davidanb01]

Very useful, as usual!!Thank you

[@MyOnlineTrainingHub]

Glad to hear that, David!

[@nick120968]

Great Stuff, thank you!

[@MyOnlineTrainingHub]

Cheers, Nick!

[@adam.antoniak]

How to display all values if we unchecked the filter? In the default settings it shows the top 5

[@dhimannaina3658]

Really helpful

[@MyOnlineTrainingHub]

Glad to hear that :-)

[@visualpmpacademy2230]

Thanks for sharing

[@MyOnlineTrainingHub]

My pleasure :-)

[@drewbbc7074]

Very nice.

[@MyOnlineTrainingHub]

Thank you, Drew!

[@sadinenim5360]

awesome

[@MyOnlineTrainingHub]

Thank you!

[@anilyadav-rt4sr]

This is cool 👍

[@MyOnlineTrainingHub]

Glad you liked it, Anil!

[@Vlog88999]

Very useful

[@MyOnlineTrainingHub]

Glad you think so!

[@moi984]

Beautifull!!

[@MyOnlineTrainingHub]

Thank you! 😊

[@mrdiv8418]

Awesome!

[@MyOnlineTrainingHub]

Glad you think so!

[@zabrus24]

Great!👍👍👍

[@MyOnlineTrainingHub]

Thanks! 😃

[@karimadel2641]

what if i need to add all remaining sub category as others after applying top N

[@user-xc7hi2wf3x]

What if I have Location, Major Category, Category Sub Category? Do I need to add Measures?

[@jpandy6464]

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]

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]

​@@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 ...

[@barttitulaerexcelbart9400]

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]

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]

@@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]

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.

[@ashrafhawwash]

It's great 👍, do you have a free course for power pivot?

[@MyOnlineTrainingHub]

Thank you! Not a free course, but a comprehensive paid course here: www.myonlinetraininghub.com/power-pivot-course

[@jayesh640]

Mam can you explain final account..

[@mdnyc10]

Can you do something like this 2013 excel versions since it doesn’t have powerpivot

[@MyOnlineTrainingHub]

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.

[@herisoaandriambeloson8229]

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]

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: kzbin.info/www/bejne/gmWlpoiwmMh_ptE

[@HooterCooter]

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]

Hi Steven, instead of the RANK formula, you can calculate % of Budget and use that field instead.

[@mahathmasadineni2884]

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]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@luisalonso3018]

Hi, how do you apply the filter to select "1" to the "include sub-category" column/measure in PowerBi? thank you

[@luisalonso3018]

Got it. In the Filters Pane... "show items when the value is 1". Thank you for your great videos!

[@MyOnlineTrainingHub]

Add 1 to the Display Top table.

[@shyyarasuri]

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]

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

[@mahathmasadineni2884]

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]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@lozitskiymv]

It is impossible to download the file

[@MyOnlineTrainingHub]

Oops, sorry about that. I've fixed the link now.

[@chrisk.9765]

The sample Excel file is not available...

[@MyOnlineTrainingHub]

Oops, sorry about that. I've fixed the link now.

[@theoriginalinvisible]

Link to download sample file not working (for me anyway!)

[@MyOnlineTrainingHub]

Oops, sorry about that. I've fixed the link now.

[@krischlapek6939]

This function isn`t going to work if instead of numbers in the "Amount" column you add text.

[@MyOnlineTrainingHub]

Correct it's designed to show the top n based on numbers.

[@krischlapek6939]

@@MyOnlineTrainingHub would you mind making another tutorial based on text please? Aka top 5 components of machinery? Thanks

[@tobayekaina8610]

Thanks for sharing

[@MyOnlineTrainingHub]

Thanks for watching!

[@tobayekaina8610]

Can't access the sample Excel file

[@eliasfayad3102]

The sample Excel file is not available

[@MyOnlineTrainingHub]

Oops, sorry about that. I've fixed the link now.