CONDITIONAL or NESTED SUMIF

[aobubo85]

Hi All,

I am trying to achieve the result in the screenshot below in a Power BI Report but having trouble with the DAX. The aim is to show an aggregate Overdraft position for parent accounts by adding negative gross balances from the sub accounts in the 2nd image below.

The table below shows that the gross balance in column E adds the principal and income only (not oincluding expense) from the same account Account A, for e.g. E2 = D2 + D3. The result in F2 for Parent 1 is a sum of only the negative Gross balances E2 + E6 to compute the Net Overdraft -600. I hope this makes sense

I have created the 2 measures below to compute the result with no success so far. Any help will be greatly appreciated:

Gross Overdraft = 
CALCULATE(

   SUM( Table 2[Balance], FILTER(Table 2, Table 2[Type] IN {Principal, Income}

)

Net Overdraft = SUMX(Table2,
      SWITCH(TRUE(),

      [Gross Overdraft] < 0, [Gross Overdraft], 0)

)

[tamerj1 · Accepted solution]

Hi @aobubo85 

please try

Net Overdraft =
SUMX (
SUMMARIZE ( Table2[Account], Table2[Account], "@GrossDraft", [Gross Overdraft] ),
IF ( [@GrossOverdraft] < 0, [@GrossOverdraft], 0 )
)

[aobubo85]

Thank you tamerj1. This worked perfectly.