Pivot Table Calculated Field

[jaustin]

Running Excel 365 Pro Plus.

Have a Table with approximately 100,000 rows.​

I tried creating a Pivot Table from this table from my Data Model, but Calculated Field is greyed out. Research indicated that using a Data Model somehow restricts Calculated Fields in a Pivot Table that is part of a Data Model. So, I created a separate Pivot Table from the main Table and now Calculated Fields option is active.​

The Pivot Table uses 3 fields from this main table (Area, Location, and Legal Subdivision) as rows. A 4th field (Status) from the main table is placed in the SumValues (as Count of Status) and Status is also placed in the Columns area. The 8 categories/values of Status are nicely displayed, summarized and sub-totaled for all of the three row fields. Now, I want to add a calculated field that utilizes the numbers in the displayed 8 column categories. The Calculated Field formula window only shows the fields from the original table. I have been unable to access the 8 categories in the formula space. I tried GetPivotData and it would not accept that notation. I can access these data successfully in a cell outside of the Pivot Table using GetPivotData. While I could do the calculations needed outside of the Pivot Table, there will be continually changes in the data/structure of the main table and keeping these calculations up to data would not be acceptable. Is there a method to get access to the data in these columns for a Calculated Field within the Pivot Table?

Thanks in advance,

jaustin

 

[Worf]

o Indeed, a pivot table created using the data model forbids calculated fields and calculated items.
o Remember that a calculated field must reside in the values area, while calculated items must be in the columns, rows or filter areas of the pivot table.
o A pivot table created from the data model can be converted to formulas at pivot table tools>analyse>OLAP tools>convert to formulas. These formulas will update when the source data changes.

 

[jaustin]

Worf,

Thank you for that information. I was not aware of your important points.