Partial sum issue in pivot table.

[jduluc12]

Hi,

Here is my data set

Aux:

LOAD * INLINE [

    ID, Amount1, Type

    1, 100, A

    1, 130, B

    2, 230, A

    2, 130, A

    2, 140, B

    3, 100, A

    3, 200, A

    3, 300, A

    3, 500, A

    4, 200, A

    4, 300, B

    4, 500, A

];

Main:

LOAD * INLINE [

    ID, Amount2

    1, 100

    2, 230

    3, 500

    4, 500

];

I am creating a Pivot table and the purpose of this table is to show

Amount from Amount1 Column if there is Type B associated with the particular ID else take the amount from Main.

Dimension:

ID

Type

Expression:

IF(sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1)))=0,

sum(Amount2),

sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1))))

The individual Amount for each ID is right but the Total is not considering the value of ID 3 which is 500. The difference between ID 3 and others is that the value of ID 3 is coming from the Main table unlike others.

Why is that?

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(

IF(sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1)))=0,

sum(Amount2),

sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1))))

, ID))

[Anil_Babu_Samineni]

Try to enable default option like Sum.

Or else, Can this works for you

Sum(Aggr(

IF(sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1)))=0,

sum(Amount2),

sum({<ID=,Type={'B'}>}(if(Match(Type,'B')>0,Amount1))))

, ID))