Aggregate children based on Parent Level calculation

[michaelsh]

Hello friends,

In my Cash flow model I have Accounts with their Groups.

Each period (month, quater, etc.) any Group can either receive cash (positive sign) or pay cash (negative sign).

I need to build 2 measures: "Net Received by Group" and "Net Paid by Group" - depending on a sign of a Group on dynamic period.

If a Group received cash (positive), than all the accounts within it have to be included in the "Net Received by Group" measure - even those accounts that have a negative sign on that period.

My thinking was more like SQL: I first get a list of groups that have positive sign, then I calculate an amount for all the accounts with these groups:

-- SQL Net Received by Group for all period

select SUM(Amt)

from Fact f join Accounts a on f.Account = a.Account

where a.Account_Group in (
   select p.Account_Group from 

   (-- all groups with positive amount

   select a.Account_Group, SUM(f.Amt)

   from Fact f join Accounts a on f.Account = a.Account

   group by a.Account_Group

   having SUM(f.Amt)>0) p

  ) g

So I wrote this DAX measure. It works on a Group or total level, but not at the account level...

It somehow detects the sign at the account level (when I have account in visual), but I need it to always check at the Group Level.

Please help.

Attached is .pbix file

https://1drv.ms/u/s!AoP_9ampPIT7gZEqIpoipQGmvDtOZg?e=BR7LP8

The measure I tried:

Net Received by Group =

VAR gr = -- Net Amount by GROUPS

ADDCOLUMNS(

SUMMARIZE(Accounts,Accounts[Account Group])

,"Net CF", [Cash Flow Amount])

VAR pos_groups = -- Only Groups with positive amount

SELECTCOLUMNS(FILTER(gr,[Net CF]>0),"group",Accounts[Account Group])

VAR acc = --all relevant accounts, those with GROUPS with positive net amounts

CALCULATETABLE(Accounts,Accounts[Account Group] in pos_groups)

RETURN

CALCULATE(CashFlow[Cash Flow Amount],acc)

[Vera_33 · Accepted solution]

Hi @michaelsh 

Are you looking for this result?

Net Paid by Group 1 = 
SUMX(VALUES(Accounts[Account Group]),IF(SUMX(ALL(Accounts[Account]),[Cash Flow Amount])<0,[Cash Flow Amount]))

Net Received by Group 1 = 
SUMX(VALUES(Accounts[Account Group]),IF(SUMX(ALL(Accounts[Account]),[Cash Flow Amount])>0,[Cash Flow Amount]))

[michaelsh]

Awesome!

Thank you, @Vera_33