8 Replies Latest reply on Jan 28, 2013 11:41 AM by Shawn Wallwork

    Distinct Average

    Chris Tsui

      Hi all,

       

      I've run into an issue with the Grand Total Aggregation which I'm hoping that I can get some help with here.

       

      I've attached a copy of an excel spreadsheet with the Data, my expected result, and what I'm getting in tableau in three seperate tabs.  Along with my Tableau packaged workbook.

       

      I've "tried" to follow along with some of the distinct Sum posts here and some I found online, but I end up comming out of it a bit mroe confused than when I started

       

      I find that when attempting to sum for the grand total I am summing the underlying data and not just the arrived at average value.  Or the Grand Total is averaging the underlying data and I am arriving at slightly different average number than desired.

       

      I think it must be a way in which the table calculation is done but I can't seem to work it out,  I've tried a few different variations but am just taking shots in the dark, so I thought i'd turn to the experts!

       

      Any help would be greatly appreciated.

       

      Thanks!

        • 1. Re: Distinct Average
          Chris Tsui

          Hi All,

           

          In my efforts to try and resolve this, I did come up with a workaround (which was to remove the distinct values in my datasource) however it doesn't seem like a scalable solution.

           

          Would appreciate any additional help if possible.  If not I can try to run some sort of function on the data source to create a new "distinct" field to use Tableau with.

           

          Thanks

          • 2. Re: Distinct Average
            Richard Leeke

            Tableau is giving you the overall average of the underlying rows, where what you are trying to get is the average of the individual customer averages for the quarter.

             

            You can get the average of the averages very easily with this calculation:

             

            WINDOW_AVG(AVG([Customer Rating]))

             

            but that will still appear on the individual rows. The Grand Total row will still be derived from the average of the underlying rows. I haven't used Grand Totals much, so I don't know if there is any way around that. I do recall seeing postings from Joe or Jonathan about that sort of stuff in the past, but haven't paid much attention.

             

            I've attached an updated workbook showing what I mean.

            1 of 1 people found this helpful
            • 3. Re: Distinct Average
              Shawn Wallwork

              I was (sort of) asked to post this anomalously earlier today. Sorry I didn't get to it until now.

               

              --Shawn

               

              EDIT: Oops, guess I should have renamed it.

              • 4. Re: Distinct Average
                Joe Mako

                Thank you Shawn,

                 

                You can see that the workbook you attached used the method Jonathan Drummey describes at http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/ (see the other two parts from more methods to work with Grand/Sub Totals)

                1 of 1 people found this helpful
                • 5. Re: Distinct Average
                  Chris Tsui

                  Thank you all for your responses!  Although it does worry me you're answering late on a Friday or worse...Joe on a Weekend!

                   

                  I do appreciate it.  I was able to incorporate the "annonymous" table calc into my workbook and got it to work.  Originally I had it within a case statement that would use a metric based on a metric parameter I created (To emulate a metric "quick filter")  When I added the calc to my metric and added the table calc I ran into the bold issue that Jonathan states in the linked article Joe added.  I realized I needed to add table calculations to all the other metrics within the case statement and eventually got it all to work!

                   

                  I actually found going through this whole exercise (and banging my head aginast it for a while) to be a worthwhile foray into getting a "little" bit more understanding into the world of table calculations.  The "partitioning" terminology was causing me a lot of confusion but when Johnathan started talking about the level of detail/aggregation, that flipped a switch in my head!

                   

                  Thank you and kudos to you all!  Now I'm curious about this "secret society" that annonymously asks Shawn to post things

                  • 6. Re: Distinct Average
                    Shawn Wallwork

                    Chris, most of us add our initials when posting back a edited workbook. That should give you a clue.

                     

                    --Shawn

                    • 7. Re: Distinct Average
                      Chris Tsui

                      Ha Ha, I had picked up on that.... I thought you might have accidentally left JM's in there and "outed" him.........

                       

                      I guess my attempt at "playing along" and feigned subtlety..........failed..... lol 

                       

                      nonetheless though, thank you and the "secret tableau society" again!

                      • 8. Re: Distinct Average
                        Shawn Wallwork

                        The secrete society says: "You're very welcome."

                         

                        --Shawn