Pivot Table partial sum

[Anonymous]

I am not able to get the pivot table partial sum if i use this formula

above(total(Sum(Sales)))

why is this happening? please see the attached file below..

also

In my pivot table, i'm using a grand total and 3 sub totals, however i only really need one sub total. Is there any way where i could hide or only calculate one sub total?

much appreciated.

[Anonymous]

for better understanding I am attaching a snapshot.

[wizardo]

Hi waqqas2426,

" I am not able to get the pivot table partial sum if i use this formula...... "

im not sure exactly what is the problem (do you get an error msg or is the value calcluated wrong?

but i have somthing to say about the formula

the above function assumes the previous line(but with regards to the chart dimensions)

but the "TOTAL" Keyword used acutely always calculates the expressions regardless of any dimensions in the chart (it only takes into account the current selection unless limited by "Set Analysis")

so use of both of them together in one expressions may lead to unexpected (tho probably valid) results.

regarding the other part of your post:

again im not realy sure what you mean.....

you don't control SUB or GRAND totals independently you can ither switch on or off, the calculations of totals for each dimension in the chart

but you get both sub and grand totals. they show or get collapsed as you navigate the pivot table.

go to the presentation tab of the Pivot's properties make sure you click on the dimesnion you wish on the leist at the left

then mark or unmark the checkbox near the text:"Show Partial Sums'

if you want to dig in deeper you can use the functions:

Dimensionality()

SecondaryDimensionality()

and with the use of an if statement you can know when a dimesnion is colpased or expnded in the pivot table.

hope my notes have contributed something to improve your situation

Mansyno

[Anonymous]

Hi Mansyno

Thanks very much for the quick reply.

I am pasting a snapshot in this reply. You can see that the partial sum is not being calculated for the formula =above(total(sum(F3)))

Can you please help me why its not calculating the partial sum when its working fine with Sum(F3)?

Also can you please send me an eample of dimensionality and SecondaryDimensionality() to calculate partial sum?

I am very much in need to solve this as urgently as possible.

Many thanks in advance.

Waqas