Excel Variance Inside The Pivot Table - Episode 2605
Microsoft Excel Tutorial: Adding a Variance inside of a pivot table.
Keani has a pivot table with current year and last year across the top. She wants a variance. Her current solution is a formula outside of the pivot table which points inside of the pivot table.
This video shows three methods for adding a variance inside the pivot table.
1. Regular pivot table, add Revenue twice. Change calculation to Difference From, Years, and (Previous Item)
2. Regular pivot table. Remove Grand Total. Use 2 Excel Calculated Items to calculate Variance and Total
3. Data Model Pivot Table. Add four DAX Measures to calculate last year, this year, total, and variance.
To download this workbook: https://www.mrexcel.com/youtube/T-yRp...
Welcome to episode 2605 of MrExcel's YouTube channel! In today's video, we will be discussing how to put a year-over-year variance inside the pivot table. This question comes from Keani, who attended one of my seminars at UCF a couple of weeks ago. She wants to know how to add the variance inside the pivot table instead of adding a formula to the right of the table. In this video, we will explore three different methods to achieve this, including using show values as difference from the previous item, adding a year to the source data, and using a data model pivot table with DAX measures.
The first method we will look at is using the show values as difference from the previous item option. This method involves adding the revenue field a second time and choosing the difference from the previous period. However, this method requires hiding columns and does not show the variance for the first year. The advantage of this method is that it is part of the pivot table and can be easily adjusted by changing the fields.
The second method involves adding a year to the source data and using a calculated item in the pivot table. This method requires updating the formula every year and removing the grand total to get the correct result. It is not the most efficient method, but it gets the job done.
The third and most efficient method is using a data model pivot table with DAX measures. This method involves adding a year to the data and creating calculated fields for sales, sales in 2020, sales in 2021, and the variance. This method allows for easy updates and provides accurate results. It is also the only method that shows the grand total correctly.
I want to thank Keani for her question and for attending my seminar at UCF. I hope this video was helpful in understanding how to add a year-over-year variance inside the pivot table. If you enjoyed this video, please like, subscribe, and ring the bell to be notified of future episodes. Don't forget to leave any questions or comments in the comment section below. Thank you for watching and see you next time for another netcast from MrExcel!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents
(0:00) Problem Statement Variance in Excel pivot table
(0:37) Show Values as Difference From
(1:52) Calculated Item in Regular Pivot Table
(3:20) DAX Measures in Data Model Pivot Table
(6:04) Wrap up
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Смотрите видео Excel Variance Inside The Pivot Table - Episode 2605 онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь MrExcel.com 30 Май 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 14,216 раз и оно понравилось 200 людям.