Sorting months chronologically and not alphabetically in Power Pivot based Pivot Tables in Excel
Вставка
- Опубліковано 28 лип 2024
- In this video, you'll learn how to customize the sorting of month names in Data Model-based Pivot Tables. Further, in this video, you will discover that Sort By Column is one of the most significant features of Power Pivot-based Pivot Tables in Excel and learn to utilize it in your reports. Using this feature gives you optimal control over your report layouts in Excel and Power BI.
This tutorial was made using Excel 365 but is applicable to older versions of Excel all the way back to Excel 2010.
Chapters:
0:00 Excel Olympics Animation
0:08 Intro
0:38 Excel Based Pivot Table Behaviour Explained
1:52 Problem with Power Pivot based Pivot Tables
2:31 Solution - Sort By Column command
3:36 Why this is a Feature
5:42 Closing Words and announcement
6:03 Bloopers
You can read the accompanying blog post here: bit.ly/3uKo8JY
Gear Used:
Camera: Canon EOS R6 - amzn.to/3e9zxML
Microphone: AKG C3000 (Analog Mic) - amzn.to/3tYd37w
Audio converter: Zoom UAC-2 - amzn.to/3dgM8wS
Laptop: Dell XPS 15 - amzn.to/3w8CyoK
Lights: Elgato Key Light - amzn.to/3rurte7
Recorded With: Camtasia - www.techsmith.com/video-edito...
Subscribe for more Excel tutorials: bit.ly/EOYTsub
Subscribe for the Excel Olympics Newsletter: bit.ly/3w3rkjK
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links that I provide I may receive a small commission with NO additional charge to you! Thank you for your support!
#Excel #ExcelOlympics #gasperkamensek
Great video! I specially liked why is this a feature part and also the bonus at the end :)
Thanks Leila. Means a lot!
Brave of you to share the bloopers. Folks don't usually see that.
My next video might be about 7 minutes long but started with 40 minutes of raw footage full of mistakes, interruptions, my chair squeaking, etc.
Great to see you starting a channel. 💥
Thanks Oz. Someone mistakes might make someone elses day 😀
I've done a lot of 'creative' things to address this problem. This is by far the cleanest explanation of this I've seen. Thanks. And, Subscribing!
Thanks JT. Means a lot!
Hi Gasper. Nice one! Great tips to get things sorted the way you want them in the final report. Thanks for sharing :)) Thumbs up!! PS - Thanks for the humorous outtakes!
Thanks Wayne. Those might not be included in every video but I will surely add it in some of the videos where there will be enough material to work with.
Awesome video! And very well explained! Thanks for sharing.
Thanks. Glad you found it helpful and informative.
Great video! , very simple and comprehensive
Thank you for your kind words Priya.
Great information, thanks for your videos!
Thank you for your kind words Anita.
Great video! solved my month old problem
Glad to hear that.
Thank you..
Thank you so much 🙏
I'm glad you found it helpful Lens Triens.
thanks!
This is a great tip, but what I really needed to know was how to make the table and correctly add it to the model in PowerPivot. My model has thousands of rows and adding a 12 row month name + number to the data model isn't working or doing anything.
Hi Laura,
This series will sadly not feature a video on adding the Date table to the Data Model. It is as simple as adding it and creating a connection to your fact table (Date on Date). I will think about a video you are suggesting, and when I publish it, I will let you know.
Finally an answer!
Glad to hear that Darryl.
Thanks for the video.
I have a Power Pivot table (not Pivot Chart) from Data Model with Month in columns and Age in Rows (to show values by Age and Month)
But I can't seem to duplicate Month by Number (added in Data Model) chronological order as you've shown?
Any advice is appreciated.
Hi. Thanks for your question. So basically if you have a Power Pivot model, and a table with month numbers and month names, then all the pieces are there and should work. If it doesn't then it's a specific case... Could you share the file...
That was very helpful but I still have one problem how would you sort months chronologically if you have to consider different years for example 2 September's in 2020 and 2021
Hi,
please take a look at my last video: ua-cam.com/video/2hgcFkpOcmc/v-deo.html. In it, you will find the two most important columns. Year-Month and Year-Month sort. and then you use the same technique as in this video to get Year-Month sorted correctly.
Create a column like “year”-“month number” (e.g. 2020-04) and sort by that (that’s what I did in my COVID-19 statistics model).
Now, I’m gonna watch that other video ;-)
Please share excel file for practice
I would love to Usman but I don't know if I still have it. I'll check and get back to you.