6 Replies Latest reply on Nov 28, 2018 10:42 AM by Deepak Rai

# Confusing Total Cost Calculation

Hi All - I am getting a little confused by my "Total Cost" calculation as the grand total value is correct but where one of my columns has a zero dollar amount the total cost for that column defaults to zero. So for example total cost for first row below should be 2.5k but its coming up as zero. I know its a simple fix and would appreciate your assistance.

Total Calculation: ifnull(SUM([Labor Cost])+Sum([Material Cost])+Sum([Service Cost]),0)

• ###### 1. Re: Confusing Total Cost Calculation

You can use the simple calculation field as SUM([Labor Cost])+Sum([Material Cost])+Sum([Service Cost])

• ###### 2. Re: Confusing Total Cost Calculation

Try This:

ZN(SUM([Labor Cost]))+ZN(Sum([Material Cost]))+ZN(Sum([Service Cost]))

• ###### 3. Re: Confusing Total Cost Calculation

Hard to tell without seeing your source data and/or the calculations behind each of your "cost" fields, but Tableau is doing exactly what you're telling it to.

There must be NULL values in one of those cost columns.  Wrap Labor, Material and Service costs (in Amit's formula) in the ZN() function to convert the NULLs to ZEROs.  Then your math will work.

In your original calculation you're telling Tableau to return "0" if it finds a NULL anywhere - so it is!

• ###### 4. Re: Confusing Total Cost Calculation

Hello Amit - Using your suggested calculation does not make a difference. Doing that does not make a difference. Just takes the zero away.

Total Cost = SUM([Labor Cost])+Sum([Material Cost])+Sum([Service Cost])

• ###### 5. Re: Confusing Total Cost Calculation

This worked! thanks a million