Pivot Table totals/partial sums with If() statements

[#1]

Hi all,

I've got a problem with the subtotals calculated in a pivot table; hopefully I may be able to get some help here.

The expression currently looks similar to this:

if(
[Product Type]= 'Type1',
sum({$<[Product SubType] = {"SubType1", "SubType2", "SubType1"}>} [Quantity]),
sum({$<[Product SubType] = {"SubTypeX", "SubTypeY"}>} [Quantity])
)

Now, I've changed my field names above but I'll explain. There are two product types, with Type1 products having quantities of SubType1, 2 and 3; Type2 products having quantities of SubTypeX and SubTypeY. Occasionally, and mostly due to inconsistencies in the data I'm pulling, a Type1 product may have SubTypeX or SubTypeY values. So something like this:

Product, Product Type, Product SubType, Quantity

A, Type1, SubType1, ##

A, Type1, SubType2, ##

A, Type1, SubType3, ##

B, Type2, SubTypeX, ##

B, Type2, SubTypeY, ##

C, Type1, SubType1, ##

C, Type1, SubType2, ##

C, Type1, SubTypeY, ##

So what I want to do is calculate the quantity of Type1, (SubType1 + SubType2 + SubType 3) and Type2 (SubTypeX + SubTypeY); while avoiding any cases where a Type1 product may calculate as (SubType1 + SubType2 + SubTypeY).

The expression above seems to work fine in my pivot table proper, however the partial sums appear to be calculated incorrectly - the number either matching the expected Type2 (the 'alternate' option of the If() expression), or offset by the precise amount of overlap on a single line item (eg, based on the above table, ## of C, SubTypeY)

I have a feeling the Aggr() function may be the solution, but I have not had any luck implementing it to avoid the problem.

Thanks in advance!

[kaushiknsolanki · Accepted solution]

Try this,

Sum(Aggr(if(
[Product Type]= 'Type1',
sum({$<[Product SubType] = {"SubType1", "SubType2", "SubType1"}>} [Quantity]),
sum({$<[Product SubType] = {"SubTypeX", "SubTypeY"}>} [Quantity])
),[Product Type],Product))

Regards,

Kaushik Solanki

[#3]

Thanks Kaushik, that seems to have worked. I added an additional dimension to the Aggr() for the month after product, as these values are being compared on a monthly basis - with that implementation everything seems to be validating perfectly.

Thanks for your help!

Sum(Aggr(if(
[Product Type]= 'Type1',
sum({$<[Product SubType] = {"SubType1", "SubType2", "SubType1"}>} [Quantity]),
sum({$<[Product SubType] = {"SubTypeX", "SubTypeY"}>} [Quantity])
),[Product Type],Product, MonthYear))