Calculated Fields Greyed out in Pivot Table with Data Model

[harv133]

I watched the YouTube Video - Budget vs. Actual - Podcast 2016 where a data model is used to compare an actuals table to a pivot table, by using intermediary "Joiner tables." It's a great solution! I am trying to expand the idea by introducing a calculated column for the difference between actual and budget, but the ability to add a calculated field (and item) is greyed out.

Does anyone know why that is or how to get around the problem?

Thanks

 

[bobsan42]

For whatever reason (honestly I haven't given it much thought or did research) you can't mix data-model with calculated fields.

So if you want to use calculated fields in a Pivot table you must uncheck Add to data model.

 

[Tubbs123]

I have a similar problem except I did not add to data model. I have a simple pivot from a table of data. When I go to calculated field item its greyed out. I've tried it on every spreadsheet I have and its the same problem. Excel 2016 on 365.

 

[RoryA]

Are you certain it wasn't added to the data model? Can you use a Distinct Count function on a value field? (If so, it's in the data model)

For a regular pivot table, calculated fields should be available as long as you have selected something in the pivot table, unlike calculated items, which are only enabled if the active cell is a non-value field.

 

[astrodon]

I have found no scenario where the Add Calculated Fields is not greyed out. It simply is not available. The good news is adding columns in Power Query and Measures in Power Pivot is actually better. A little more work but better. I am inclined to believe MS is forcing this by disabling the subject item.