CONDITIONAL or NESTED SUMIF

aobubo85 — Tue, 20 Jun 2023 16:25:51 GMT

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)

)

Re: CONDITIONAL or NESTED SUMIF

tamerj1 — Tue, 20 Jun 2023 17:01:56 GMT

Hi aobubo85

please try

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

Re: CONDITIONAL or NESTED SUMIF

aobubo85 — Sun, 25 Jun 2023 23:44:06 GMT

Thank you tamerj1. This worked perfectly.

topic Re: CONDITIONAL or NESTED SUMIF in DAX Commands and Tips

CONDITIONAL or NESTED SUMIF

Re: CONDITIONAL or NESTED SUMIF

Re: CONDITIONAL or NESTED SUMIF