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: Excel Variance Inside The Pivot Table - Episode 2605 Sample Files - MrExcel Publishing
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
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: Excel Variance Inside The Pivot Table - Episode 2605 Sample Files - MrExcel Publishing
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
Transcript of the video:
How to put a year-over-year variance inside the pivot table.
Today's question from Keani, who was in my seminar at UCF a couple of weeks ago.
She says, "How to put the variance inside the pivot table?
Currently, they're just adding a formula to the right of the pivot table".
And there's three different methods in this video, one using show values as difference from the previous item, but then you have to hide two columns. I hate that.
Or add a year to the source data and then just a calculated item like a regular pivot table.
Or the third one, which is probably the best, is a data model pivot table and then adding four DAX measures. Let's take a look.
Okay. Here's one way to do this.
Insert pivot table. Okay.
Put dates across the top. We're going to roll those up to years.
Click okay. Revenue here.
Anything you want down the side, sector, customer, whatever.
I'm going to take that revenue field and add it a second time, which seems counterintuitive.
Go to sum of revenue 2 and show values as a difference from, and this is really important here, you have to choose year, whatever that thing is called, years, and from the previous period.
Click okay. All right.
Now, the hassle is there's no variance for 2020 because it doesn't have the year before.
So you have to hide that column.
So now you have 2020, 2021, and then the 2021 variance. And then you would hide this column.
All right?
The advantage of this is it's actually part of the pivot table.
So take sector out, put customer back in, and it continues to work, right? It's pretty cool.
So I don't like that we have to hide the columns, but it's a quick and easy way to go.
All right. Method two.
Unfortunately, you can't do the year roll up in the pivot table.
You have to do it here.
Just add it somewhere to the original data set. Format as a number, no decimal places.
Double click to copy that down. All right.
Insert pivot table. Okay.
Sector down the side, year going across the top, revenue here.
And you have to choose one of these two headings up here.
Go to fields, items and sets, a calculated item.
It's going to be called Variance, and that is going to be equal to the later year minus the earlier year.
I hate this method because every year you're going to have to update this formula. It's something you do once a year.
Who's ever going to remember that? And we can click okay.
So now we have 2020, 2021 in Variance, but now the problem is the grand total's wrong.
So we're going to right click and remove grand total.
And then from here we're going to insert another calculated item, which we'll call Total.
And this is going to be equal 2020 plus 2021.
Click okay. All right?
So the total of those two, 634361. Yep, that works.
Our answer's right. Method two.
Okay, method three. We have to get a year in here.
You can either put it in the table or in a separate table.
And then insert pivot table.
This time, though, check the box for add this data to the data model.
Sector down the left-hand side. Good.
Now we're going to add some calculated fields. So right click the table name, say add measure.
This'll be called Sales, which is going to be the sum of revenue.
Click okay. Okay.
Right click add measure. This will be called Sales2020.
We're going to use equal calculate. This is like SUMIFS.
And we want to calculate the sales amount. What's that called?
I call it Sales, right? Yeah.
S-A-L-E-S.
Where the range year is equal to 2020.
This is another one you're going to have to update every year.
All right. So we get Sales2020.
It's currency, zero decimal places. Click okay.
And I should be able to add that.
Okay, great. And then range, add measure.
This one's going to be called Sales2021.
Boy, if I was smart, I would have saved that formula for before.
So we're going to calculate sales where range year is equal to 2021. Currency, zero decimal places.
We'll add that one. All right.
And then the Variance, this is cool, come up here again, right click add measure, Variance is going to be equal to Sales2021 minus Sales2020.
Currency, zero decimal places. Yeah.
And I think I'm going to put Sales in here to get your grand total and Variance there.
There we are.
Well, hey, I want to thank Keani for showing up at my seminar at UCF, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
Today's question from Keani, who was in my seminar at UCF a couple of weeks ago.
She says, "How to put the variance inside the pivot table?
Currently, they're just adding a formula to the right of the pivot table".
And there's three different methods in this video, one using show values as difference from the previous item, but then you have to hide two columns. I hate that.
Or add a year to the source data and then just a calculated item like a regular pivot table.
Or the third one, which is probably the best, is a data model pivot table and then adding four DAX measures. Let's take a look.
Okay. Here's one way to do this.
Insert pivot table. Okay.
Put dates across the top. We're going to roll those up to years.
Click okay. Revenue here.
Anything you want down the side, sector, customer, whatever.
I'm going to take that revenue field and add it a second time, which seems counterintuitive.
Go to sum of revenue 2 and show values as a difference from, and this is really important here, you have to choose year, whatever that thing is called, years, and from the previous period.
Click okay. All right.
Now, the hassle is there's no variance for 2020 because it doesn't have the year before.
So you have to hide that column.
So now you have 2020, 2021, and then the 2021 variance. And then you would hide this column.
All right?
The advantage of this is it's actually part of the pivot table.
So take sector out, put customer back in, and it continues to work, right? It's pretty cool.
So I don't like that we have to hide the columns, but it's a quick and easy way to go.
All right. Method two.
Unfortunately, you can't do the year roll up in the pivot table.
You have to do it here.
Just add it somewhere to the original data set. Format as a number, no decimal places.
Double click to copy that down. All right.
Insert pivot table. Okay.
Sector down the side, year going across the top, revenue here.
And you have to choose one of these two headings up here.
Go to fields, items and sets, a calculated item.
It's going to be called Variance, and that is going to be equal to the later year minus the earlier year.
I hate this method because every year you're going to have to update this formula. It's something you do once a year.
Who's ever going to remember that? And we can click okay.
So now we have 2020, 2021 in Variance, but now the problem is the grand total's wrong.
So we're going to right click and remove grand total.
And then from here we're going to insert another calculated item, which we'll call Total.
And this is going to be equal 2020 plus 2021.
Click okay. All right?
So the total of those two, 634361. Yep, that works.
Our answer's right. Method two.
Okay, method three. We have to get a year in here.
You can either put it in the table or in a separate table.
And then insert pivot table.
This time, though, check the box for add this data to the data model.
Sector down the left-hand side. Good.
Now we're going to add some calculated fields. So right click the table name, say add measure.
This'll be called Sales, which is going to be the sum of revenue.
Click okay. Okay.
Right click add measure. This will be called Sales2020.
We're going to use equal calculate. This is like SUMIFS.
And we want to calculate the sales amount. What's that called?
I call it Sales, right? Yeah.
S-A-L-E-S.
Where the range year is equal to 2020.
This is another one you're going to have to update every year.
All right. So we get Sales2020.
It's currency, zero decimal places. Click okay.
And I should be able to add that.
Okay, great. And then range, add measure.
This one's going to be called Sales2021.
Boy, if I was smart, I would have saved that formula for before.
So we're going to calculate sales where range year is equal to 2021. Currency, zero decimal places.
We'll add that one. All right.
And then the Variance, this is cool, come up here again, right click add measure, Variance is going to be equal to Sales2021 minus Sales2020.
Currency, zero decimal places. Yeah.
And I think I'm going to put Sales in here to get your grand total and Variance there.
There we are.
Well, hey, I want to thank Keani for showing up at my seminar at UCF, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
