unable to create Calculated field in pivot table

In a PivotTable, the “There’s a problem with the formula” message when creating a calculated field is usually caused by one of these issues:

1. PivotTable is based on an OLAP source
   • Calculated fields and items cannot be created if the PivotTable is connected to an OLAP (Analysis Services) data source.
   • Only PivotTables based on non-OLAP source data support formulas for calculated fields/items.
   • Check the PivotTable’s source: if it is OLAP, calculated fields are not allowed and the message will appear regardless of the formula.
2. Using invalid elements in the formula
   • In PivotTable calculated fields/items:
     • Cell references and defined names cannot be used.
     • Only field names, item names, constants, operators, and allowed functions can be used.
   • If the formula includes something like =A2*0.15 or a named range, it will fail. Instead, use the field name, for example =Sales*15%.
3. Syntax or semantic errors in the formula
   • Syntactic errors: missing parentheses, commas, or incorrect function syntax.
   • Semantic errors: referring to a non-existing field/item, wrong number/type of arguments to a function, or type mismatches.
   • Ensure all field and item names are spelled exactly as in the PivotTable field list. Use single quotes around names with spaces, numbers, or symbols (for example, ='Total Sales'*1.1).
4. Formula behavior specifics
   • Calculated field formulas operate on the sum of the underlying data, not on individual records. The formula must be written in terms of fields, not rows.

To troubleshoot:

1. Confirm the PivotTable is based on a non-OLAP source.
2. Rewrite the formula using only field names and constants (no cell references or named ranges).
3. Check spelling and quoting of field/item names, especially if they contain spaces.
4. If still failing, simplify to a very basic formula like =1 or =Sales to confirm calculated fields are allowed, then build up the formula step by step.

References:

• Calculate values in a PivotTable
• Create Formulas for Calculations in Power Pivot
• Calculated items in Excel and Excel Services
• Create PivotTable or PivotChart views in an Access desktop database