Hi Chris, is there a method to recalculate the Total or Grant Total of a product or division based on a "inserted Calculated Item"? I have added a Calculated field to automatically work out the year on year growths for products or divisions which works fine, but the Total or Grand total "Sum's" the individual lines instead of calculating the growth correctly. Do you have a video that maybe explains how to fix this? Thanks
This is great but as soon as I start using it on large data it takes forever to calculate and then everytime I make a change in the pivot it's very clunky and takes 5 minutes to update. Is there a way to make this more efficient?
Any idea how to make this work on averages? I have multiple invoices for one product in a year and want to take the average and compare it to the average of that product in the prevoius year. Doing calculated item tells me Excel does not support this on averages.
Dear Chris, I have a question about calculated field in Pivot table. It seems I can only use the fields from the table where I have all data. Is it possible to use calculated fields and use one column (field) from the pivot table itself in calculated field formulas?
Well if I click on Calculated Items this appears to me "If one or more fields in the PivotTable have calculated items, no fields can be used in the data area two or more times, or in the data area and another area at the same time. If you are trying to add a field, remove the calculated items and add the field again. If you are trying to add a calculated item, change the PivotTable report so that no field is used more than once and then add the calculated item." Now please help me how can I use calculated item in excel.
I have the same issue. Also how can I calculate the difference between two column data. Number is submission (submitted 8) and number of errors (4 errors), want the next column to calculate and show 50%. The first cell in the roll is the month (under month it’s by location). No matter what I do it always gives me a #DIV!. And the show value as does not help as I don’t want the errors to be calculated by the total errors. Been trying to figure out for 2 days, about to give up and just go with manually creating a column outside of the pivot table to calculate but then I run into the error of not being able to sort the percentage from largest to smallest due to the data tied to the data in the pivot table. Ugh…
This was amazing, it was hard before to understand calculated items :) thank you for the effort done
Happy to help.
Thank you for this, best explanation out of several I've looked at
Awesome. Thank you.
Amazing trick changing those numbers to percentage! I've been using the hold Ctrl method for years. Thanks Chris 👍🏼
You are welcome RW.
Wow! It's like magic. Thanks Chris.
My pleasure!
Smart Chris Menard. Thanks a lot. Great tutorial.
You're welcome!
Any examples with more then 2 items ? More the. 2 years?
Than you very very much!! This is so helpful and easy to understand!
You're very welcome!
Nice, thanks! Now I understand Calculated Items. Thanks
I KNEW there was a way to do this! Thank you very much!! This is a real life saver... ;-)
Wow i thought i knew everything about PivotTables... Thanks
You are welcome.
Great video, love the tips and tricks. Keep 'em coming
More to come! I LOVE Excel.
Well explained!. Thanks Chris
My pleasure!
You are a Star. Thank you so much.
Thank you, this is really helpful!
Thank You :)
You're welcome!
Hi Chris, is there a method to recalculate the Total or Grant Total of a product or division based on a "inserted Calculated Item"? I have added a Calculated field to automatically work out the year on year growths for products or divisions which works fine, but the Total or Grand total "Sum's" the individual lines instead of calculating the growth correctly. Do you have a video that maybe explains how to fix this? Thanks
Genius
This is great but as soon as I start using it on large data it takes forever to calculate and then everytime I make a change in the pivot it's very clunky and takes 5 minutes to update. Is there a way to make this more efficient?
How do you make a change to the formula once you created the new item?
Any idea how to make this work on averages? I have multiple invoices for one product in a year and want to take the average and compare it to the average of that product in the prevoius year. Doing calculated item tells me Excel does not support this on averages.
Dear Chris, I have a question about calculated field in Pivot table. It seems I can only use the fields from the table where I have all data. Is it possible to use calculated fields and use one column (field) from the pivot table itself in calculated field formulas?
But sir When we are doing at Grand total and Subtotal not coming correct
Can u make it in columns?
Yes, I made a video on that recently. When I return from vacation, I'll find it. It was posted within the last two weeks.
Well if I click on Calculated Items this appears to me "If one or more fields in the PivotTable have calculated items, no fields can be used in the data area two or more times, or in the data area and another area at the same time. If you are trying to add a field, remove the calculated items and add the field again. If you are trying to add a calculated item, change the PivotTable report so that no field is used more than once and then add the calculated item." Now please help me how can I use calculated item in excel.
I have the same issue. Also how can I calculate the difference between two column data. Number is submission (submitted 8) and number of errors (4 errors), want the next column to calculate and show 50%. The first cell in the roll is the month (under month it’s by location). No matter what I do it always gives me a #DIV!. And the show value as does not help as I don’t want the errors to be calculated by the total errors. Been trying to figure out for 2 days, about to give up and just go with manually creating a column outside of the pivot table to calculate but then I run into the error of not being able to sort the percentage from largest to smallest due to the data tied to the data in the pivot table. Ugh…
The blog post of calculated items chrismenardtraining.com/post/calculated-items-in-a-pivottable
Many thanks! This is super helpful!