"The item cannot be added or modified" when adding a Calculated Item in and pivot table.

[alex1alex]

Hello,
I'm trying to add a Calculated Item to a pivot table but get the below error when clicking on the "Calculated Item..." menu.

The table that the pivot is based on comes from a PowerQuery connection.
(If I cut and past the data into a separate sheet and (so that it's not connected to the Query) I don't get the error)

Any ideas?

The query does not load to the data model, (A PivotTable based on the "Data Model" has the Calculated Items.. menu greyed out)

Is the concept of a Calculated Item possible using DAX? (this is what I'm trying to accomplish: Pivot Table Calculated "Row" )

The item cannot be added or modified There may not be enough memory, the maximum number of items allowed in a field may have been reached, or if a Visual Basic macro is performing the operation, the macro may be incorrect.

Simplify the PivotTable report or check the macro for errors, and then try the operation again.

Thanks!
Alex

 

[RoryA]

You could certainly add a couple of columns in PQ that produce the new item name and adjust the value so that it's either 1* the original or 0.5* the original depending on the original item name. Then your pivot doesn't need to do anything other than group and sum as usual using the 2 new columns.

 

[alex1alex]

Thanks Rory. Suggesting PQ was good and I ended up with the below table and doing an inter-join with the data table in the other thread. (the real data is a bit more complicated and it would be a lot more than 2 columns.)
This lets me keep the Pivot Table simple.

The calculated Item seemed elegant and it's a shame that didn't work. Weird Error messages that hide what the problem are the worse.

Regards,
Alex

Fruit	Fruit Group	Multiplier
Apple	CH-Apple	1​
Cherry	CH-Apple	0.5​

 

[RoryA]

To be honest, calculated items have always been limited and tend to lead to terrible performance anyway.