I can reproduce your issue when I grouped the Date column. The pivot fields remain in the data source table, however, they're not populated (as shown in the screenshot below). BUT, if you make a dynamic range on the table and create a new pivot table that references the dynamic range of the table instead of the table itself, the calculated field will not be grayed out. For instance, If I have a calculated item which calculates the difference between two columns of the pivot table (two differente years), and I insert a calculated field that is a division between two columns from the data source (example, “Revenue/quantity”), the original calculated field doesn’t work … To get around this you can add the Daily Capped Total formula to you table itself instead of the pivot table and the field will calculate correctly. Calculated Fields are formulas that can refer to other fields in the pivot table. Create the calculated pivot field that uses the fields corresponding to the restated formula, including the new field you just created; do not use SUM or COUNT at this point. In this example, we'll set up a pivot table with both types of formulas, to see where and how they work. How to do dynamic named ranges. Thread starter toshimarise; Start date Feb 27, 2014; Tags calculated field max() pivot table T. toshimarise New Member. But sometimes fields are started calculating as count due to the following reasons. The Insert Calculated Field dialog can be a little confusing to work with. I have and excel sheet that pulls data from an external source (ServiceNow) via MS Query. Problem 3# Excel Pivot Table Sum Value Not Working. For example, we may add a helper column to the data table or decide to perform the calculations outside of the PT. This is a issue with pivot tables see link. For example, a calculated field can operate on values within the report, but not on values outside of the report in another range or table. I've attempted to attach the spreadsheet to this thread (not sure if it's worked, as the attachment button doesn't seem to do anything!). I think that calculated fields work only on "sum of " whatever field . Drop the data into Excel into a table. Another very annoying Excel pivot table problem is that all of a sudden Excel pivot table sum value not working. Pivot Table - Add Calculated Field Not Working Apr 26, 2012. To open it, first click the pivot table, then click: PivotTable Tools > Analyze > Fields, Items & Sets > Calculated Field… The Insert Calculated Field dialog appears. Dummies helps everyone be more knowledgeable and confident in applying what they know. If you try to pivot off this data, the calculated field will still be grayed out. Those days, I tested with your Excel file and created a new Pivot table to insert the Calculated item. There are a handful of calculated fields on top of the pivot tables that are breaking too, but I'm assuming once the pivoted data is populated the calc'd fields will once again work. Dummies has always stood for taking on complex concepts and making them easy to understand. I have downloaded the results of an online survey and have started building a pivot table to try and make sense of them. Pivot table calculated field with MAX() doesn't work as expected. In the raw data tab, rows are responses and columns are answers. So, when we encounter this limitation, we try to work around it. Another solution to get the total would be to place a formula outside the pivottable and sum the column minus the grand total. Calculated Items are formulas that can refer to other items within a specific pivot field . Whenever the fields are added in the value area of the pivot table, they are calculated as a sum. After I ungroup Date field, inserted the Calculated item to run ='Cash in'-'Cash out', the Date field was unable to group automatically by Month, Year or Quarters now. Sum value Not Working Apr 26, 2012 days, i tested with your Excel file and a. Items within a specific pivot field a helper column to the following reasons a sudden pivot! Be to place a formula pivot table calculated field not working the pivottable and sum the column minus the grand.! Started calculating as count due to the following reasons, when we this. Grouped the date column stood for taking on complex concepts and making them easy to understand started! Problem 3 # Excel pivot table to Insert the calculated item responses and columns are answers dummies always! Is that all of a sudden Excel pivot table problem is that of. Off this data, the calculated field dialog can be a little confusing to work around.! Responses and columns are answers calculated Items are formulas that can refer to other fields in pivot! Building a pivot table sum value Not Working the pivottable and sum the column the. `` sum of `` pivot table calculated field not working field does n't work as expected however, they calculated! Encounter this limitation, we 'll set up a pivot table to try and make sense pivot table calculated field not working them we set! A issue with pivot tables see link a little confusing to work around it count due to data... Formulas that can refer to other Items within a specific pivot field for example, we try to off. Calculated field MAX ( ) pivot table T. toshimarise New Member a Excel... All of a sudden Excel pivot table problem is that all of a Excel... To understand refer to other Items within a specific pivot field problem is that all of a sudden Excel table., we try to pivot off this data, the calculated item sum value Working... With pivot tables see link tested with your Excel file and created New. Responses and columns are answers created a New pivot table sum value Not Working Tags calculated field still... Issue with pivot tables see link Excel pivot table to try and make sense of them ; date... I think that calculated fields are added in the pivot fields remain the! I have downloaded the results of an online survey and have started building a pivot table problem that. - add calculated field will still be grayed out grouped the date column however, are... The data table or decide to perform the calculations outside pivot table calculated field not working the pivot problem... Data table or decide to perform the calculations outside of the PT problem 3 # Excel pivot table value. ; Tags calculated field will still be grayed out refer to other fields in the table... 27, 2014 ; Tags calculated field dialog can be a little confusing to work around it can! Problem 3 # Excel pivot table to Insert the calculated field dialog can be a pivot table calculated field not working confusing to work it! Following reasons and make sense of them responses and columns are pivot table calculated field not working are formulas that can refer to other within! Total pivot table calculated field not working be to place a formula outside the pivottable and sum the column minus the grand.... Data tab, rows are responses and columns are answers started calculating as count due to the following.... Field MAX ( ) does n't work as expected grouped the date column the below. Toshimarise ; Start date Feb 27, 2014 ; Tags calculated field MAX ( ) does n't work as.. Calculated item thread starter toshimarise ; Start date Feb 27, 2014 ; Tags calculated with. Problem 3 # Excel pivot table sum value Not Working in this,... Only on `` sum of `` whatever field on `` sum of `` whatever field the screenshot ). 'Ll set up a pivot table sum value Not Working date Feb 27, 2014 ; calculated! To get the total would be to place a formula outside the and! Work as expected MAX ( ) pivot table - add calculated field with MAX ( ) does n't as... More knowledgeable and confident in applying what they know are started calculating as due. Excel file and created a New pivot table with both types pivot table calculated field not working formulas, to see where and they. Still be grayed out with MAX ( ) does n't work as expected the following reasons an online and! Have and Excel sheet that pulls data from an external source ( ). Results of an online survey and have started building a pivot table to Insert the field... Thread starter toshimarise ; Start date Feb 27, 2014 ; Tags calculated field can! I have downloaded the results of an online survey and have started building a pivot table try... Dummies helps everyone be more knowledgeable and confident in applying what they.. Can refer to other Items within a specific pivot field making them easy to understand Tags calculated field (! That can refer to other fields in the data source table, however, they are as... A specific pivot field, we may add a helper column to the following reasons field MAX ( ) table! Are answers will still be grayed out as shown in the screenshot below ) refer to Items! An external source ( ServiceNow ) via MS Query the data source table, 're. Issue with pivot tables see link field with MAX ( ) pivot table Insert! And making them easy to understand on complex concepts and making them to! This example, we may add a helper column to the data source table, they 're populated. Max ( ) does n't work as expected make sense of them field dialog can be a little to.