I'm working with a sales dataset and I need to combine "Shipping Cost" and "Product Base Price" into a single "Total Cost" metric. I tried simply dragging both onto the shelf, but that just creates two separate axes. How do I write a calculation that adds these two measures together at the row level? Also, how should I handle the calculation if some rows have null values in one of the columns, as my current attempts are returning blank results?
3 answers
To sum two columns in Tableau, you need to create a Calculated Field. Right-click in the Data pane and select "Create Calculated Field." In the editor, simply write your formula: [Column A] + [Column B].
However, a common trap is the "Null" issue. In Tableau, Value + Null = Null. To prevent this, wrap your fields in the ZN() (Zero Null) function, which converts nulls into zeros. Your final formula should look like this: ZN([Shipping Cost]) + ZN([Product Base Price]). Once created, you can drag this new measure into your view just like any other field.
Is there a difference between doing this calculation in the "Data Source" tab versus doing it inside a "Worksheet"? I noticed that when I do it in the worksheet, the aggregation changes depending on what dimensions I add to the rows.
If you have multiple columns to sum, you can also use the IFNULL() function as an alternative to ZN(), allowing you to specify a different default value if zero doesn't make sense for your specific data model.
I agree with Brenda. ZN() is the fastest way for math, but IFNULL() is great for flexibility. I recently used it to sum several tax columns where I needed the fallback to be a baseline rate rather than just zero. It keeps the data much cleaner for complex financial reporting.
David, that’s a great observation. If you create the calculation as [A] + [B], Tableau adds the values for every single row first, and then sums them up based on your view (Row-Level Calculation). If you write it as SUM([A]) + SUM([B]), Tableau sums all of A and all of B separately based on your filters and then adds the two totals together (Aggregate Calculation). For "Total Cost," you generally want the row-level version to ensure accuracy across different levels of detail.