3 Replies Latest reply on Mar 22, 2016 6:18 PM by Takaaki Koseki

# How to calculate the average from Totals from different columns

Hi all, I'm facing an issue in table which is really simple to calculate in Excel and I just fonder if someone has any idea how to fix it in Tableau, or at least a workaround about it.

Below is how the report should looks like:

The first table is done already and looks like the following:

As you can check the values match!

The issue is when I try to average by Female/Male and then by Floors:

The calculation for the second table (Female/Male) in excel should be:

AVERAGE(960,759) = 859

AVERAGE(1473,1398) = 1435

I was expecting to reference the cells somehow in Tableau but I didn't find a way to do it

The calculation for the third table (Floors) in excel should be:

AVERAGE(960,473) = 1216

AVERAGE(759,1398) = 1078

I was expecting to reference the cells somehow in Tableau but I didn't find a way to do it

The second table I was pretty much able to get it but the numbers has some small difference that I don't know why.

Female and Male Fixed calculations are:

{ FIXED [Survey Date],[Floor #]: SUM([Female  Entrants])}

{ FIXED [Survey Date],[Floor #]: SUM([Male  Entrants])}

the number expected are:

AVERAGE(960,759) = 859

AVERAGE(1473,1398) = 1435

and as you can see there are a small difference but they doesn't match

For the third table I wasn't close at all, I couldn't get it work which should be something easy to do, at least in excel.

Any idea how to calculate the average from Totals from different columns?

If the example is not clear, please let me know .

Thanks!!!!

Sebastian

• ###### 1. Re: How to calculate the average from Totals from different columns

Hi.

The following calculation means...

```AVG( { FIXED [Survey Date],[Floor #]: SUM([Female  Entrants])} )
```

So you got 865 and 1437.

If you want AVERAGE(960,759) = 859 and AVERAGE(1473,1398) = 1435 then...

```AVG( { FIXED [Floor #]: AVG({ INCLUDE [Survey Date]: SUM([Female Entrants])}) } )
```

It calculates sum of each floor and date first,

and next calculates average of each floor.

The nested LOD expression inherits the outer dimension.

And third one is...

```( AVG( { FIXED [Floor #]: AVG({ INCLUDE [Survey Date]: SUM([Female Entrants])}) } )
+ AVG( { FIXED [Floor #]: AVG({ INCLUDE [Survey Date]: SUM([Male Entrants])}) } ) )
/ 2
```

Thanks.

• ###### 2. Re: How to calculate the average from Totals from different columns

You did a great job! the solution is almost perfect, one thing that I would ask if you know the way to also the calculation handle the situation when you change the Survey Date, I've noticed that even I have the Survey Date as a Quick filter the calculation won't change, the calculation keep stick with the result and I would like to be able to change when the filter change.

Appreaciate if you have a way to handle this situation as well.

Thanks!

Sebastian

• ###### 3. Re: How to calculate the average from Totals from different columns

Then use INCLUDE instead of FIXED like...

```AVG( { INCLUDE [Floor #]: AVG({ INCLUDE [Survey Date]: SUM([Female Entrants])}) } )
```

FIXED has characteristics that not effected by quick filter.

(This is very useful in some situation!)