
1. Re: How to calculate the average from Totals from different columns
Takaaki Koseki Mar 22, 2016 2:55 AM (in response to Sebastian Caffarelli)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
Sebastian Caffarelli Mar 22, 2016 5:20 AM (in response to Takaaki Koseki)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
Takaaki Koseki Mar 22, 2016 6:18 PM (in response to Sebastian Caffarelli)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!)
Please see also this article. It explains the calculation order of Tableau.
Tinkering With LOD Expressions & Percent of Total « Tableau Tinkering