Missing Duplicates in pivot table

[#1]

Hello,

Whenever I hide a column in a Pivot Table, any duplicated data that was in that column is now, not totaled up for example:

I have some entries that go something like this:

x1682 -1510416.67
5416.67
-1510416.67
-135583.33
-1510416.67
-41583.33
-1197916.67
-23083.33
-1197916.67

However, when I minimise x1682, the second two -1510416.67 and the first -1197916.67 is not calculated into the final total (which should be -6975000) I get -2756250. Note: there is no DISTINCT in the formula.

Has anyone else experienced this before and is there a way around it?

Thanks for your help in advanced

Mark

[Oleg_Troyansky]

You guessed the reason - because some dimensions are hidden...

The way around it (I think...) would be to use advanced aggregation and force the aggregation to happen for all the relevant dimensions, including the hidden ones. For example:

sum( AGGR(NODISTINCT sum(Sales), Dim1, Dim2, Dim3) )

[#3]

Thanks Oleg,

I'm going to have a look into it today.

Mark