Calculated items in pivot table are changing on their own

[Sam_NY]

I have 3 calculated items in a pivot table - each just sums 3 or 4 other items from the field. Some of the items are summed in multiple calculated items. For example: 'Subtotal1' = 'Item 1' + 'Item 2' + 'Item 3' and 'Subtotal2' = 'Item 3' + 'Item 4' + 'Item 5'

Twice now the formulas seem to have changed completely on their own. The first time, just one calculated item was affected (as in: if the orig was 'Subtotal1' = 'Item 1' + 'Item 2' + 'Item 3', it changed to something like 'Subtotal1' = 'Item 1 + 'Item 4' + 'Item 5')

When I fixed it, I triple checked to make sure it was adding right. It worked for a while, but now the same field and one other field are messed up. This time, the two calculated items that got corrupted appear to have *switched* formulas. So when I view the formulas via the solve order window, 'Subtotal1' now has the formula that used to be associated with 'Subtotal2' and vice versa.

I had some graphs that I'd exported (as images) before it got corrupted the second time, which confirm that it USED to be calculating correctly and now is not. (In other words, I didn't just make a mistake when I fixed it the first time).

Anyone know why this may be happening and, importantly, how to safeguard against it? Important to note here is that none of the calculated items includes any of the OTHER calculated items in the formula (so solve order presumably shouldn't matter).

 

[mike_302]

Same question from me. I would like to bump this thread back up.

I can add that the Calculated Items change their definition after I update my data sources, reimporting all the data. But to be clear, nothing in the data is changing -- just more data is added to my data set, within the boundaries of the existing data.

 

[Joyner]

Maybe someone else will know exactly what is going on, but I'll just say I know that Calculated items can do funny things, but I have mostly seen things happen when you create more than one Pivot Table from the same cache, what you do on one Pivot Table may affect the other(s)

 

[Sam_NY]

I never found a solution to this and now build in a manual spot check to my process whenever I have calculated items (which is annoying).

That's an interesting hypothesis, Joyner. I definitely had more than one pivots built off the same data table and calculated items and fields are shared across pivots. I wonder if the fields/columns are indexed in a specific way on the back-end and then between adding new columns to the data and refreshing one of several pivots, it gets the fields wrong in the calculation. I'd personally rather it break than display results of a now-incorrect calculation.

Anyway, this seems like an Excel bug, so absent any workarounds, not sure what can be done. How does one report a bug to Microsoft, incidentally?

 

[SpillerBD]

Now that PowerPivot is available to all current O365 subscriptions, go with it.
Calculated columns or defined measure are WAY BETTER !! Can also solve some issues in order of operations.