8 Replies Latest reply on Oct 11, 2018 7:20 AM by Simon Runc

# Grouping Formula

Dear All,

I am just starting with Tableau and i need to do for our company a KPI which is based on multiple column calculation such as below:

Column1
Column2Column 3KPI
102030A*B-C = 10*20-30 = 170
56031A*B*C = 5*60-31 = 269

This can easily be done by creating a calculated field in the table but i also need to have this formula updated if i agregate all the results such as this:

Column1
Column2Column 3KPI
158061A*B-C = 15*31-61 = 1139

I hope my question is clear as i could not find any answers on the forum by doing the search. but maybe i did not use the right keywords.

Best regards,

Gary

• ###### 1. Re: Grouping Formula

hi Garry,

So with regards your first part, you can do this as a "row level" calculation in Tableau. This works the same as if you'd done it in the data

[Column1] + [Column 2] - [Column 3]

To get an aggregated result, at what ever level is in you viz, you'd change it to

SUM([Column1]) + SUM([Column 2]) - SUM([Column 3])

so the values are SUMed before being combined.

One note that if any of your columns are blank it will return null (1+1+null = null), so you might want to wrap a ZN around the fields

zn([Column1])+ zn([Column 2]) - zn([Column 3])

This just means nulls will be treated at zeros. Worth noting when using calculating involving multiple fields (unless you'd want a null, of course!)

• ###### 2. Re: Grouping Formula

Hello Gary,

You can try below approach:

KPI = sum([Column1])*sum([Column2]) -sum([Column 3])

• ###### 3. Re: Grouping Formula

Hi Simon,

Thanks a lot for your feedback and help, I did it in a tableau file, so it is easier. When you say we can do it in a “row level” calculation, you mean a calculated field in the view?

I added the following formula: zn()+ zn() - zn() as proposed by you

Now this is the starting table:

And this is the final result:

So it looks like it is working for the moment.

I will try to get the real formula I need to use and get back to you.

Thanks a lot

Gary

• ###### 4. Re: Grouping Formula

hi Gary,

Glad it helped. So by Row Level and Aggregate Calculation I mean that for some calculation types (row level) the calculation is performed row-by-row in the data, so the result of that calculation is irrelevant of what is in the VizLoD (the dimensions on the canvas), but for aggregate calculations the result is dependent on what is on the canvas. I refer to these as Off Canvas and On Canvas calculations. I wrote a Quora answer on this, that you might find useful (basically, IMHO, if you get this concept, you get Tableau!)

hopefully the quora post helps see how

[Column1] + [Column 2] - [Column 3]

and

SUM([Column1]) + SUM([Column 2]) - SUM([Column 3])

are different.

In the first one the  [Column1] + [Column 2] - [Column 3] is done on every row and then you bring in the result as a SUM (say) at whatever level you have on the canvas

on the SUM([Column1]) + SUM([Column 2]) - SUM([Column 3]) the SUM is done at whatever level is on the canvas, and then the results of that are then used in the 1 + 2 - 3.

Hope that helps

• ###### 5. Re: Grouping Formula

Hi Simon,

I am sorry to say but it is not working, I have just tried with the following formula:

Stock Quantity / Forecast 1: as you can see it is ok when I am at the lowest level (figure 1) but when I aggregate by removing one of the row field (in this case IPC) then the FMC calculation 2 is not recalculation the formula but just summing the data of all the IPC in the column (Figure2)

Sorry but I am lost and I cannot find a solution.

Regards,

Gary

Figure1

Figure2:

I am also including the file in case this help you to help me â˜º

Thanks,

Gary

Regards,

Gary

• ###### 6. Re: Grouping Formula

I'm out for the rest of the day, but try using this version

zn(SUM([Column1])) + zn(SUM([Column 2])) - zn(SUM([Column 3]))

which should work at any level.

• ###### 7. Re: Grouping Formula

Dear Simon,

Thanks a lot, it looks like it is working. I hope now to be able to do this with the real data.