Microsoft Excel tutorial on pivot tables.
Todd wants to calculate (12/52)*Sales on each subtotal row in a pivot table.
Bill shows how to add a calculated field to a pivot table and then uses some formatting to show the field only on the subtotal rows.
To download the data: Excel Add A Calculation To Each Pivot Table Subtotal Row Epsiode 2591 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Formula outside of pivot table
(0:28) Calculated Field in Pivot Table
(1:11) Verify the calculation
(1:30) White Font trick
(1:55) When pivot table changes
(2:35) Wrap-up
Todd wants to calculate (12/52)*Sales on each subtotal row in a pivot table.
Bill shows how to add a calculated field to a pivot table and then uses some formatting to show the field only on the subtotal rows.
To download the data: Excel Add A Calculation To Each Pivot Table Subtotal Row Epsiode 2591 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Formula outside of pivot table
(0:28) Calculated Field in Pivot Table
(1:11) Verify the calculation
(1:30) White Font trick
(1:55) When pivot table changes
(2:35) Wrap-up
Transcript of the video:
Todd wants to add a calculation to each pivot table subtotal row.
Take a look at this pivot table that Todd sent me.
He has two items in the row fields here, Person and Project.
So we have Person subtotals after each group of Projects.
And Todd wants to do a calculation of 12 divided by 52, times sales at that subtotal row.
And currently, you can see it's just a formula outside of the pivot table.
And that's not really good because as we add more projects, those are going to move around. So here's my solution.
Select a cell inside the pivot table, and we want to be like on that Sum of Sales heading.
And then on the pivot table Analyze tab, Fields, Items & Sets, create a new Calculated field.
I don't know, what is it? Is this a bonus or an allocation?
Let's call it an allocation. I don't know actually what it is for Todd.
And so here we're going to say 12 divided by 52, times sales, which is going to do that at every single row in the underlying data. Click okay.
And let's just do a little test here. Equal 12 divided by 52, times the 74,141.
17,109.46.
All right, so right there, that number, that number, and those two numbers are the numbers that Todd wants to be in the pivot table, but he doesn't want all the other numbers.
I've thought about different ways to do this and unfortunately, the way that I came up with that is just the easiest to explain is select all of those cells and here in the font dropdown, choose a white font.
It looks really good. I mean, it looks like we did the right thing.
And if we just printed it, his manager's never going to know.
Now the place where this is going to come out, let's create a new project here.
So Andy will create a P99.
And then when I refresh the pivot table, yeah, we're going to have to go back and change the font on that extra cell to be white.
Yeah, I thought about editing the MDX, I thought about making a data model pivot table, I thought about DAX, but I think at the end of the day, the white font is probably the way to go.
Let me know what you think down in the YouTube comments. Do you have a better way?
I want to thank Todd for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another net cast 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.
Take a look at this pivot table that Todd sent me.
He has two items in the row fields here, Person and Project.
So we have Person subtotals after each group of Projects.
And Todd wants to do a calculation of 12 divided by 52, times sales at that subtotal row.
And currently, you can see it's just a formula outside of the pivot table.
And that's not really good because as we add more projects, those are going to move around. So here's my solution.
Select a cell inside the pivot table, and we want to be like on that Sum of Sales heading.
And then on the pivot table Analyze tab, Fields, Items & Sets, create a new Calculated field.
I don't know, what is it? Is this a bonus or an allocation?
Let's call it an allocation. I don't know actually what it is for Todd.
And so here we're going to say 12 divided by 52, times sales, which is going to do that at every single row in the underlying data. Click okay.
And let's just do a little test here. Equal 12 divided by 52, times the 74,141.
17,109.46.
All right, so right there, that number, that number, and those two numbers are the numbers that Todd wants to be in the pivot table, but he doesn't want all the other numbers.
I've thought about different ways to do this and unfortunately, the way that I came up with that is just the easiest to explain is select all of those cells and here in the font dropdown, choose a white font.
It looks really good. I mean, it looks like we did the right thing.
And if we just printed it, his manager's never going to know.
Now the place where this is going to come out, let's create a new project here.
So Andy will create a P99.
And then when I refresh the pivot table, yeah, we're going to have to go back and change the font on that extra cell to be white.
Yeah, I thought about editing the MDX, I thought about making a data model pivot table, I thought about DAX, but I think at the end of the day, the white font is probably the way to go.
Let me know what you think down in the YouTube comments. Do you have a better way?
I want to thank Todd for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another net cast 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.
