Conditional sum in Tableau

0
votes

I am just starting out with Tableau and have a question.

I have a star schema data warehouse with a fact table containing an "Amount", joined to a dimension containing "Event types"

Two event Types are "Settled" and "Cancelled"

I want to create a calculated measure in Tableau "Net Amount" which is the difference between the sum of Settled and Cancelled.

How do I do that?

I tried

SUM(IIF(Event_type_name = "Settled",Amount,0))-SUM(IIF(Event_type_name = "Cancelled",Amount,0))

but it returns a blank string

1
tableau-api
Man, just tested here with sample coffee chain database, it works just fine. SUM(IIF([Market] = "Central", Sales, 0))-SUM(IIF([Market] = "East", Sales, 0)). Can you send a twbx so I can take a look? – Inox

1 Answers

4
votes

After experimentation (and asking Tableau support), it turns out my formula was "correct" but was returning blank because of null's in the amount column.

So I needed to use the ZN function to convert nulls to zero's in the feed.