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
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.
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?
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!
Thank you for this, best explanation out of several I've looked at
Awesome. Thank you.
The blog post of calculated items chrismenardtraining.com/post/calculated-items-in-a-pivottable
Wow i thought i knew everything about PivotTables... Thanks
You are welcome.
Than you very very much!! This is so helpful and easy to understand!
You're very welcome!
Smart Chris Menard. Thanks a lot. Great tutorial.
You're welcome!
Any examples with more then 2 items ? More the. 2 years?
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... ;-)
Well explained!. Thanks Chris
My pleasure!
Genius
Great video, love the tips and tricks. Keep 'em coming
More to come! I LOVE Excel.
You are a Star. Thank you so much.
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
Thank you, this is really helpful!
But sir When we are doing at Grand total and Subtotal not coming correct
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.
How do you make a change to the formula once you created the new item?
Thank You :)
You're welcome!
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?
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.