5 Replies Latest reply on May 4, 2017 10:29 AM by Elliott Stam

    how to solve average of averages in reference line

    Shen Tat

      Hi Everyone!

       

       

      attached is both my source data, a quick example in excel of my problem. and a workbook

       

      as you can see by the reference line, tableau is averaging the average weekly scores.

       

      this is skewing the overall average of the entire line

       

      these represent QA scores

       

      for any selected CSM Alias in the legend (there are 3 of them), i need tableau to show the average across all of the lowest level of scores

       

      the difference to the managers who use this graph is between an excellent employee, and just a good one

       

      can anyone help me?

        • 1. Re: how to solve average of averages in reference line
          Elliott Stam

          Hi Shen,

           

          Happy to help, but I need some clarification. What exactly do you mean by "average across all of the lowest level of scores"? My interpretation of this is that you would want the average of of CSM Alias aare in this case, since it is the overall lowest in the chart. But if you click on aare in the color legend, that's exactly what you get - an average of aare's KANA Score (95.75%).

           

           

          Are you wanting to always show aare's average in this chart (without clicking in the legend automatically see the reference line at 95.75% insteadof 97.19%)?

           

          Or, perhaps you're trying to find the lowest score for each CSM Alias and average that result? If so, let me know if this is the direction you want to go...

           

           

          If you can lay it out for me and give me a target to hit, I'm pretty sure I can make this work for you.

           

           

          Elliott Stam - InterWorks

          • 2. Re: how to solve average of averages in reference line
            Shen Tat

            Hi Elliot, I do not think I am being understood. First, Let's look at the data.

             

                

            TargetObsIDCSM_AliasRevDateKANAScore
            1306crff4/10/201794.44%
            1308crff4/10/201794.12%
            1343aare4/12/201794.44%
            1383crff4/18/2017100.00%
            1389aare4/18/2017100.00%
            1390aare4/18/201794.12%
            1398chrdnh4/18/2017100.00%
            1400chrdnh4/18/201794.74%
            1416chrdnh4/11/2017

            100.00%

             

            here is the source.

             

            Now, I'm visualizing the data week over week. So, some weeks there will be only 1 measure to show a mark, some weeks there will be several measures to show the mark. The measures are being aggregated by the Week. That's the problem.

             

            When I select aare as my CSM Alias we have the bolded subset of data above.

             

            in Excel I can demonstrate what I need to have happen...

             

             

            Avg. KANAScoreWeekly Avg. KANAScore
            100.00%100.00%
            94.74%97.37%
            100.00%
            98.25%98.69%

             

            I need the reference line to show 98.25% which is the average against all of the measures across the line on the graph. Not what I am seeing, which is 98.69%, a skewed number, because there is a second aggregation (avg) happening with the two already aggregated weekly averages.

             

            I hope that helps demonstrate what I need.

             

            and I hope you can help.

            S

            • 3. Re: how to solve average of averages in reference line
              Elliott Stam

              Hi Shen,

               

              Based on what you said, my understanding is that you want this output:

               

              Table for verification:

              The formula needed was a level of detail calculation:

              This formula calculates the average KANA Score for each CSM Alias and always outputs the same value regardless of how your Tableau view is aggregated.

               

              Hope that helps! (Workbook attached for reference)

               

               

              Elliott Stam - InterWorks

              1 of 1 people found this helpful
              • 4. Re: how to solve average of averages in reference line
                Shen Tat

                OMG Elliot, YES!!! thank you so much the syntax of this formula is really unfamiliar to me... is there a reference document / site which lists such formulae?

                • 5. Re: how to solve average of averages in reference line
                  Elliott Stam

                  That function is what's called a level of detail calculation, which can be pinned to one of three levels: 'FIXED', 'INCLUDE', and 'EXCLUDE'.  A fixed level of detail is what I tend to use the most, and it explicitly specifies the level of aggregation for your formula to be computed. The 'include' variety allows you to include extra dimensions which may or may not be in your visual (for example, if you were looking at a map of countries but your formula specified to include cities then metrics in that worksheet would be aggregated at the city level rather than country). The 'exclude' variety is the opposite of 'include', allowing you to specify certain dimensions to ignore.

                   

                  Fixed is the most globally useful and applicable, in my opinion. It's great because regardless of how you construct your view, that number will always be the same. That becomes extraordinarily useful when building complex dashboards, or when you need a 'single truth' type of number which should stay the same no matter what.

                   

                  Here's some info on calculations in Tableau, in general: Functions

                  Here's a useful and practical set of examples of level of detail calculations: Top 15 LOD Expressions | Tableau Software

                   

                  Glad this helped you out!

                   

                  Elliott Stam - InterWorks