2 Replies Latest reply on Jan 8, 2018 1:18 PM by Mark Brentnall

    How to Calculate Average of Aggregate Data

    Mark Brentnall

      Hi! I'm new to Tableau and this is my first time posting. I have searched and found some threads that answer similar questions (managing aggregate data appears to be a common challenge in Tableau), but I'm still struggling to implement the suggested solutions, so thought I'd reach out to the community...

       

      In this example, I'm tracking the performance of a user who has performed 4 different exercises a certain number of times (the number of attempts, or sessions, can be variable for each exercise). The user is scored on 3 different metrics for each exercise (for example "Time to Complete", "# of Errors", "Style Points").

       

      My worksheet displays the data for "User max score", "User min score" and User avg score" for each metric for each exercise aggregated over all attempts. For each metric, I would like to display a "total" that is the average over all 4 exercises of the aggregate values for max, min and avg scores.

       

      Under the menu "Analysis -> Totals" I have selected to "Show Column Grand Totals" and I have selected "Total all using -> Average". But the averages are not what I expected. For example, if I look at "User max score" for "Metric 1", for the average I would expect (3.22+6.41+1.70+2.86)/4 = 3.54, but the Average calculation from "Totals" is showing 5.49. Even if I "weight" each of the exercise values by the number of sessions (attempts), I still get a different number than is reported. I'm confused over how Tableau is using the aggregate value to produce a column total.

       

      I apologize ahead of time... due to the sensitivity of the data, I cannot share the packaged workbook without significantly modifying the data sources, so I hope the screenshot of my workbook pasted below and the description above is sufficient to request help...

       

      Thanks in advance for any suggestions!

       

      Mark

        • 1. Re: How to Calculate Average of Aggregate Data
          Norbert Maijoor

          Hi Mark,

           

          Thanks for your post. Could you share your workbook in .tbwx format?

          Sharing it will "speed-up" the solution time tremendously;)

           

          Regards,

          Norbert

          • 2. Re: How to Calculate Average of Aggregate Data
            Mark Brentnall

            Hi Norbert,

             

            As I wrote in my original request, there is some sensitive data contained in my worksheet and data sources that I can't share publicly (hence the screenshot where I've even covered up some of the field names with "generic names"). I could try to create a sample worksheet with sample data, but that would require quite a bit of re-work. However, if I don't get any solutions, soon, I will consider that...

             

            I was able to come up with a partial solution by reviewing other posts (e.g. Getting the Average of underlying Aggregate data ) and using the WINDOW_AVG function. I created 3 new calculations that use the WINDOW_AVG function on the original data and then apply it "along table down" to correctly come up with the average values.

            But these are repeated for each exercise row (compare screenshot below, Worksheet 2, with original - the last 3 values for each exercise labeled "Curriculum Avg..." are the results of the WINDOW_AVG function). Unfortunately, I'd like these averages to appear at the top of the table, like the "Analysis -> Totals" feature provides.

            But, by removing the original data from the Measure Values shelf, "hiding" all but one row of exercises, and then turning off "Show Header" for the first two columns (Exericse Name and # Sessions, which are no longer appropriate), I can get a view that's close to what I want: showing just the average of the data (see below screenshot, Worksheet 3). This is pretty hacky, and I'm sure there's a much better way to do this, but my Tableau skills are not good enough (yet!) to figure this out.

             

            Any suggestions would be much appreciated!

             

            Thanks again,

            Mark