7 Replies Latest reply on Oct 10, 2019 10:51 AM by Branden Kornell

# Conditional aggregation on tableau

Trying to create a conditional calculated field that looks at the value (actuals,targets,difference) of the dimension and divides costs by leads per value and then calculates the difference between these.

This is my code:

`SUM(CASE [row_name]WHEN 'targets' THEN [t_spend]/[leads]WHEN 'actuals' THEN [t_spend]/[leads]WHEN 'difference' THEN (SUM(IF([row_name]='targets') THEN([t_spend]/[leads] - SUM(IF([row_name]='actuals') THEN([t_spend]/[leads]))END) END`

`)`

This is my desired output:

`           cost leads c/leadtargets      1000 10 100 actuals       500 10 10diff          500 0 90`

• ###### 1. Re: Conditional aggregation on tableau

You can't do this in Tableau the way you can in Excel (which is kind of how your thinking and calculations look to me).

What you can do is make a simpler calculation

And then create a crosstab with your targets/actuals dimension on Rows, and Measure Names on columns.

If you want to make the Difference line, that's much more complicated. Tableau has table calculations for Difference, but that will put the differences on the 'targets' and 'actuals' row.

You can use a hack for all your measures if you want to make the 'Grand Total' row into a Difference row. There are many examples in the forum, this is one:

• ###### 2. Re: Conditional aggregation on tableau

Thanks, would I be able to use this solution to create a differences row as opposed to a column, sorry maybe I'm a bit new to tableau because I'm confused as to how to do that

• ###### 3. Re: Conditional aggregation on tableau

Yes; there's no real difference between rows and columns other than how things are arranged on the screen. The calculations work the same in rows and columns. A quick way to flip rows and columns is to use this shortcut in the top icon bar.

Can you share a workbook? That will help people give you better feedback on your request.

• ###### 5. Re: Conditional aggregation on tableau

It looks like you basically have your data set up to accomplish what you want.

The only tricky part is [c/lead]. It will calculate a true average for each row; sounds like you looking to calculate the difference between the above two averages. In that case, you can do it with a table calculation:

if min([row_name]) <> 'difference' then

ELSE

END

Make sure it is set to Compute Using --> Table (Down). You can make the column header neater by changing its alias under Measure Names.

• ###### 6. Re: Conditional aggregation on tableau

seems to be defaulting to calculate across, and its not changing that when I try change to calculate down

• ###### 7. Re: Conditional aggregation on tableau

It doesn't matter how it's set up in the calculation. It should default to Table (down) when you put it in the viz; if not, change it.