How to calculate combining several columns using restrictions?

[#1]

Hi,

I am trying to solve a data problem. I need to create a formula that takes the sum (based on column A) of "to X" value minus the corresponding value in "balance" for X. The similar will apply for the "to Y". Another restriction is that variables in column "C" will differ, so there must be a restriction for this as well.

A	B	C	Balance	To X	To Y	TO ALLOCATE X	TO ALLOCATE Y
1	X	Z	508.98	12.41	496.57
1	Y	Z	12,334.00	300.83	12,033.17	-195.74	195.74
2	X	Z	492.59	12.01	480.58
2	Y	Z	21,118.62	515.09	20,603.53	34.51	-34.51

The two last columns "TO ALLOCATE X" and "TO ALLOCATE Y" shows the numbers that I would like to achieve.

Can anyone please help me with this issue?

Thanks,

Johanna

[Jason_Michaelides]

Can you explain again how you achieve your desired result please?  I can't work out how you got to -195.74, for example.

Thanks,

Jason

[#3]

Hi,

In order to get -195.74 I do 12.41+300.83-508.98. For Y I get 195.74 I do 496.57+12,033.17-12,334.

Thanks,

Johanna

[#4]

Something like this?

[#5]

Hi,

Is it possible to send the expression instead? I don’t have a full license (yet), so I cannot open your file.

Thanks!

[#6]

I use a straight table with "A" as dimension.

To Allocate x: =Sum([To X]) - Sum({<B = {'X'}>}Balance)

To Allocate y: =Sum([To Y]) - Sum({<B = {'Y'}>}Balance)

[#7]

Hi,

Thanks for this, but the thing is that I need to have all columns that indicated in the first posting. Any ideas how to solve this?

Thanks,

Johanna

[CELAMBARASAN · Accepted solution]

Hi,

     Try with this expression

    To Allocate X =Sum(TOTAL <A> [To X])-Aggr(Sum( {<B={'X'}>}Balance),A)

    To Allocate Y=Sum(TOTAL <A> [To X])-Aggr(Sum( {<B={'Y'}>}Balance),A)

Hope it helps

Celambarasan