Subtracting the sum of specific records from the sum of another group of records

[JFDemers]

Hi,

I am facing a problem that I can't seem to tackle on the right angle...

I have an account number 12345 with its market value, let's say 1,000,000$

Other accounts can hold units of that account 12345. Let's say account 23456 & 34567 each holds 100,000$ of 12345.

I want to calculate the value of 12345 in which the result will be 1,000,000 - sum(100,000 + 100,000) = 800,000.

The way the data is structured is the following:

Account Number     SecurityID      MarketValue     'Field representing account number if  the case'

12345                            ABC                    1,000,000           NULL

23456                            XYZ                      500,000              NULL

23456                            MNO                   100,000               12345

34567                          DEF                     400,000               NULL

34567                           MNO                   100,000               12345

So in short I want to subtract the sum of each records that are representing account 12345 from the sum of the records of account 12345.

Hope I am being clear.

Thx

[Kushal_Chawda · Accepted solution]

@JFDemers  try below

Data:
LOAD [Account Number],
SecurityID,
MarketValue
FROM Source;

left join(Data)
LOAD [Representing Account] as [Account Number],
     Sum(MarketValue) as [MarketValue Representing Account]
FROM Source
where len(trim([Representing Account]))>0;
group by [Representing Account];

Now you can use the expression in chart as below

Sum(MarketValue)-sum([MarketValue Representing Account])

[JFDemers]

Works perfectly!

Thx a lot!