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

    How to calculate the average from Totals from different columns

    Sebastian Caffarelli

      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:

      Excel.jpg

       

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

       

      Tableau report 1.jpg

      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.

       

      table 2.jpg

       

      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