3 Replies Latest reply on Jan 7, 2014 7:06 AM by Ramon Martinez

# How to average multiple measures on a per-row basis, as a new calculated field

Hello folks, hopefully a simple one here.

I have a series of temperature readings over time, such as that shown below

TimeT1T2T3Tavg
17172

70

71
271717171
371737272
472727272
572727171.7

In my data, I do not have the TAvg column.  However, I need it to use as a new value (each temperature reading is not, by itself, significant), which I could then view along with any other measure, perform table calculations on, etc.  I could create this very easily via excel or as an additional calculated value with SQL, however it seems to me that this should be easy to do in Tableau.  But, I cannot seem to create this calculated field. As a slightly complicating factor, some of the readings are NULL for some time steps, so I can't simply sum and divide by 3.

Again - all I want to do is add an additional column to my imported data, which is a calculation across other columns.

Is there a good method for this?

• ###### 1. Re: How to average multiple measures on a per-row basis, as a new calculated field

Tableau is a bit tricky when it comes to handling Null values. See Handling Null Values | Tableau Software

You may want to use this formula in calculated field

(ZN(T1)+ZN(T2)+ZN(T3))/3

If this doesn't work, you may want to look up this discussion.

http://community.tableau.com/message/178353

Or, you can always replace blanks by 0 in your excel file before importing into Tableau.

Cheers !

• ###### 2. Re: How to average multiple measures on a per-row basis, as a new calculated field

Thank you for the answer Manas.  Unfortunately two problems: 1) I don't want to average in zeroes, and 2) if I simply add them up, ignore the nulls, and divide by 3 I will get an inaccurate value -- for example, 70+70+(null) / 3 provides 46.7 as the answer, though the average should be 70 for this line.

• ###### 3. Re: How to average multiple measures on a per-row basis, as a new calculated field

Hi John,

It is not clear how your data is structured in your datasource but my suggestion is to have it normalized instead of having a crosstab like the table you post shows.

Something like this:

 Time reading temperature 1 T1 71 1 T2 72 1 T3 70 2 T1 71 2 T2 71 2 T3 71 3 T1 71 3 T2 73 3 T3 72 4 T1 72 4 T2 72 4 T3 72 5 T1 72 5 T2 72 5 T3 71 6 T1 70 6 T3 75

Take a look at the attached workbook with the solution.

I hope this helps

best

Ramon