How do I write this formula/expression? - I need the sum of a column - based on type...

[Tiger1]

I need to finish this expression to sum everything under [Document Type] - excluding "Payments" but minus everything "Credit Memos"

This is what I have so far:

SUM(
SELECT(AR[90 + Days Virtual],
AND([Customer Number] = [_THIS].[Customer Number],
[Document Type] <> “Payments”
      )
    )
)

[graham_howe · Accepted solution]

I think you are almost there, just have one sum taken away from another

SUM(
  SELECT(
    AR[90 + Days Virtual],
    AND(
      [Customer Number] = [_THIS].[Customer Number],
      AND(
        [Document Type] <> “Payments”,
        [Document Type] <> “Credit Memos”
      )
    )
  )
) - 
SUM(
  SELECT(
    AR[90 + Days Virtual],
    AND(
      [Customer Number] = [_THIS].[Customer Number],
      [Document Type] = “Credit Memos”
    )
  )
)

Note I added another condition to the first SUM to also exclude credit memos, otherwise I think you would be counting them twice.

[Tiger1]

Thank you very much.  I will try this now...

[Tiger1]

k.  I am still not getting what I wanted.  So I am wanting the rows to be shown ONLY if there is a balance.  So, if in 0-30 days there is a balance - then show the value, and if there is no balance in 0-30 days - show nothing (Blank).

Using the formula i built along with yours - now I get the entire balance for 0-30 days on each row: