Excel Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-2473879%22%20slang%3D%22en-US%22%3EExcel%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2473879%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20latest%20office%20365%20microsoft%20excel.%20I%20have%20created%20a%20pivot%20table%20with%20multiple%20tables.%20I%20wish%20to%20create%20and%20separate%20column%20for%20some%20calculation%20based%20on%20pivot%20table%20date.%20After%20researching%20I%20found%20that%20I%20can%20use%20calculated%20field%20option%20in%20Calculations%20tab.%20But%20the%20option%20gets%20greyed%20out%20when%20I%20use%20power%20pivot%20or%20multiple%20tables%20for%20one%20pivot%20table.%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2473879%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2474140%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2474140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1085772%22%20target%3D%22_blank%22%3E%40sameerbhardwaj1490%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThe%20option%20is%20greyed%20out%20because%20Calculated%20field%20translate%20in%20either%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcalculated-columns-in-power-pivot-a0eb7167-33fc-4ade-a23f-fb9217c193af%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECalculated%20column%3C%2FA%3E%20or%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcreate-a-measure-in-power-pivot-d3cc1495-b4e5-48e7-ba98-163022a71198%23%3A~%3Atext%3D%2520To%2520Create%2520a%2520Measure%2520in%2520the%2520Power%2CPress%2520Enter%2520to%2520accept%2520the%2520formula.%2520More%2520%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EMeasure%3C%2FA%3E%20in%20Power%20Pivot.%20In%20other%20words%20what%20you%20get%20is%20expected%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETwo%20options%3A%3C%2FP%3E%3COL%3E%3CLI%3EYour%20various%20Tables%20have%20same%2Fsimilar%20content%2C%20then%20you%20can%20combine%20them%20with%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fabout-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EGet%20%26amp%3B%20Transform%3C%2FA%3E%20-%20aka%20Power%20Query%20-%20as%20a%20single%20Table%20that%20becomes%20the%20Source%20of%20your%20Pivot%20Table%20where%20you'll%20be%20able%20to%20add%20Calculated%20fields%3C%2FLI%3E%3CLI%3EYou%20create%20Calculated%20column(s)%2C%20ideally%20Measure(s)%20in%20Power%20Pivot%20(%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fdax%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EDAX%20reference%3C%2FA%3E)%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi 

I am using latest office 365 microsoft excel. I have created a pivot table with multiple tables. I wish to create and separate column for some calculation based on pivot table date. After researching I found that I can use calculated field option in Calculations tab. But the option gets greyed out when I use power pivot or multiple tables for one pivot table.

Please help.

1 Reply

@sameerbhardwaj1490 

Hi

The option is greyed out because Calculated field translate in either Calculated column or Measure in Power Pivot. In other words what you get is expected

 

Two options:

  1. Your various Tables have same/similar content, then you can combine them with Get & Transform - aka Power Query - as a single Table that becomes the Source of your Pivot Table where you'll be able to add Calculated fields
  2. You create Calculated column(s), ideally Measure(s) in Power Pivot (DAX reference)