Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Something like this?
Hi,
Is it possible to send the expression instead? I don’t have a full license (yet), so I cannot open your file.
Thanks!
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)
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
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