7 Replies Latest reply on Mar 31, 2016 6:14 AM by Boas Lee

    Row Level Averages

    Jon Zerden

      I am having trouble figuring out how to do row-level averages.

       

      In the image below - we have instructors and each instructor has taught a number of distinct classes (red arrow) with a total attendance across all of their classes in the blue arrow.  I am trying to determine by instructor, how many people on average attend their class.

       

      I attempted to build a simple calculated field --- but it seems to be taking the total class count into consideration when presenting the result set.

       

      Thanks

       

       

      3-29-2016 2-28-28 PM.png

        • 1. Re: Row Level Averages
          Boas Lee

          Hi Jon,

           

          When doing Calculations in Tableau Desktop, one major concept to keep in mind is the level of aggregation the calculation is being done. For example, there is a big difference between sum(sales/profit) vs. sum(sales)/sum(profit).  If my data looks something like this

           

          Sales Profit

          10     20

          5      2

           

          sum(sales) / sum(profit) =(10+5) / (20+2) = 15/22 = .68

          vs.

          sum(sales/profit) =10/20 + 5/2 = .5 + 2.5 = 3

           

          From the explanation, it looks like sum(sales/profit) is something you might be looking for.

           

          For more information about aggregations, please look at the links below.

          About Aggregate Calculations

           

          If this does not resolve the issue, please attach a sample workbook with an explanation of desired result including what number is being shown and what number is desired.


          Regards,

          Boas.

          • 2. Re: Row Level Averages
            Jon Zerden

            Thanks Boas-

             

            that's helpful.  That being said, leveraging your example - i'd actually want to be adding a third column on the right which would be showing 20/10 = 2 in row 1 and 5/2 = 2.5 in row 2.  Do you have a recommended approach / calculation accordingly?

            • 3. Re: Row Level Averages
              Boas Lee

              Hi Jon.

               

              There are a couple of things I would like to clarify.

               

              1. In the last comment, you said that row 1 should be 20/10. Did you mean 10/20 = .5? 20/10 would be profit/sales not sales/profit.

              2. Tableau Desktop is created as a visualization tool and not a database. Tableau Desktop does not have the ability to manipulate the data within the database.

              3. That being said, Tableau Desktop does have a separate table that it creates.

              3. When creating a calculated field, Tableau Desktop will add a new column to Tableau's table.

              4. It is possible to see Table's table by looking at the underlying data (please look at the following link View Data )

              5. By adding the calculated filed sum(sales/profit), this should add the a new column in Tableau's table where row 1 = .5 (10/20) and row 2 = 2.5 (5/2)

               

              Please look at the attached workbook.

               

              I hope the clarifications helps. Please respond if there are any further questions.

               

              Regards,

              Boas

              • 4. Re: Row Level Averages
                Jon Zerden

                I still think you aren't fully getting the challenge.

                 

                Let's look back at my example...

                 

                Currently I have 3 columns:

                Instructor name | Attendance Count | Number Of Classes

                 

                • Instructor name is a fact
                • Attendance count is a measure constructed by creating a 'count' in the underlying table
                • Distinct Class Count is a measure constructed by created a 'count distinct' in the underlying table

                 

                I then created two calculations:

                 

                Calc1: [Attendance Count]/[Distinct Class Count]

                Calc 2: sum([Attendance Count]/[Distinct Class Count])

                 

                See image below... but:

                 

                1. The values returned for both are the same in every row

                2. If we look at row 2 (Mark) -- you;ll see attendance of 1796 - with Class count of 543 (red)

                --- Calc 1 and Calc 2: Show teh value of 3

                --- I am expecting 1796/543 = 3.31 -- instead I am getting 2.9144

                3. If we look at the blue row  - i'd expect to see 18/7 = 2.57 -- instead we get 0.0323

                3-30-2016 9-53-14 AM.png

                 

                3-30-2016 10-03-37 AM.png

                 

                3-30-2016 10-01-51 AM.png

                 

                Thoughts?

                • 5. Re: Row Level Averages
                  Boas Lee

                  Hi Jon,

                   

                  Looking at the screen shots, it seems that the calculation should the following.

                   

                  Formula:

                  sum([Attendance Count])/sum([Distinct Class Count])

                   

                  It is very difficult for me to further assist without a workbook. Would it be possible to post a workbook?

                   

                  Please note that Calculation 1 and calculation 2 will result in the same result.

                   

                  In Tableau it is possible to assign an aggregation within a calculation. If an aggregation is not assigned, it is possible to change the aggregation of the calculation by simply right clicking the calculation pill. The following calculation: [Attendance Count]) / [Distinct Class Count] is the same thing as sum([Attendance Count]) / [Distinct Class Count]) when the aggregation is set to sum. It is possible to change the aggregation (min,max,average, etc.) for calculation 1 but it is not possible to change to change the aggregation for calculation 2 because the aggregation of "sum" has already been defined within calculation 2.

                   

                  I hope that this gives a little more clarification.

                   

                  Regards,

                  Boas

                  • 6. Re: Row Level Averages
                    Jon Zerden

                    I figured it out - the correct equation was COUNT([Check In Count])/COUNTD([Distinct Class Count])

                     

                    Thanks for your help

                    • 7. Re: Row Level Averages
                      Boas Lee

                      Hi Jon,

                       

                      I am happy to hear that the issue was resolved. If possible, please mark this question as resolved.

                       

                      Hope you have a great day.

                       

                      Regards,

                      Boas