Calculated Fields Greyed out in Pivot Table with Data Model

harv133

New Member
Joined
May 10, 2019
Messages
1
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
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

New Member
Joined
Oct 29, 2019
Messages
4
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,821
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top