Pivot Table - Partial Sum

[pkelly]

I have a pivot table showing product family and quantity sold.

The product familes have different units e.g. Sawn has m3 whilst Machined as M.

For this reason I cannot show totals.

However, I would like to turn totals on if the user selects a combination of families where the quantity units are the same.

e.g. It is okay to show Sawn and Panels as we analyse the sales of these in m3.

I know how to work out if it is okay to show the totals order not but am unsure how to affect the "Show Partial Sums" tick box in the Pivot Table Properties | Presentation tab.

Think this will need to be done via a macro...

Any help greatly appreciated...

Regards

Paul

[wizardo]

hmmm

hi paul

it is possible ....... but im not sure how elegant it looks

here is the recepie

first you create macro in the module editor

call it what ever you like ( i called it "partial_sum") an here is its contents:

sub partial_sum

' replace the field name with the field that hold the quantity units
set val=ActiveDocument.Fields("your field name").GetPossibleValues
' replace the object ID "CH02" with the object ID of your pivot
set chart = ActiveDocument.getsheetobject("CH02")
set cp = chart.GetProperties
set dims = cp.Dimensions

if val.Count = 1 then
dims(1).ShowPartialSums = true 'show sums
chart.SetProperties cp
else
' replace the zero in dims(0) with the number of the dimension you want to toggle the partial sum (zero based)
dims(0).ShowPartialSums = false 'do not show sums
chart.SetProperties cp
end if

end sub

now go to the menu "setting / document properties " and select the "Triggers" tab

in the "Field Event Triggers" find your field and put the action "run macro" on both the "On Change" and "On Select" events

thats should do it

now when ever the user selections make it so that only one "quantity unit" is available the pivot will show partial sum

otherwise it will not

hope that helps

[johnw · Accepted solution]

Looks like it's even simpler than I'd have expected. Assuming you have a unit of measure associated with each product:

if(len(UOM),sum(Amount))

See attached. Select a single product, or multiple products with the same unit of measure, and the partial sums will appear.

[wizardo]

hmmmm

i can see its working but i dont understand it jhon,

as far as i know an IF function returns null if the ELSE part is missing, so how come thers a value showing???

[pkelly]

Like Wizardo, I can see that this is working but have no idea how...

Is it possible that you could talk us through what this is doing?

[johnw]

An if() statement doesn't require an else. And if without an else only returns null when the condition is false. It turns out that's exactly the behavior we need.

Let's look at what happens on the individual rows. At the detail level, every row has a UOM associated with it, so len(UOM) is > 0, which is interpreted as true (false=0, true=everything else), so we return sum(Amount). The detail rows will therefore always have the sum.

Now what about at the partial sum level? Well if EVERY row in the partial sum has the same UOM, then len(UOM) is still > 0, so we'll return sum(Amount), and we'll see the partial sum. But if ANY row in the partial sum has a different UOM, then there are multiple UOMs, so UOM will be null, so len(UOM) will be 0, which is interpreted as false, so we'll return the ELSE, but there is no ELSE, so we just return null. Nulls are then suppressed by default, so the partial sum row itself is then suppressed.

Now, in the data set I posted, every customer had every product. But let's say customer A only buys Cars and Widgets. The even if other customers buy other things, customer A will always show the partial sum since there is only one UOM associated with customer A. I'm guessing that's still the behavior you'd want, but I thought I'd mention it.

It might make things more clear if we wrote it like this:

if(count(distinct UOM)=1,sum(Amount),null())

But the shorter version I used does exactly the same thing.