5 Replies Latest reply on Jun 23, 2016 1:53 PM by Daniel Teo

    Aggregating aggregate values

    Chris Lozier

      I'm sure there is an easy way to solve this challenge.


      My org administers a survey of a few hundred questions. At a level above the questions are about 40 "indicators," each of which may have 1-12 questions. At a level above the indicators are 15 "standards," again each with one to a small handful of indicators.


      Here is the challenge: I'm interested in averaging metrics at every level, but I don't want all averages to be question-weighted. Indicators will be, of course, but I want standards to be the equally weighted averages of the indicators underneath them.


      I'm working on cleaning a tbwx, but thought someone might have a solution off the top of their head.



        • 1. Re: Aggregating aggregate values
          khalid norat

          If you can send over a sample data set or a sample workbook would really help in understanding what you are trying to achieve.

          A lot of what you say sounds possible but would need some trial an error to get you to the right result. So any visual aid(screen shots) or even better (excel or twbx) would be a great help

          • 2. Re: Aggregating aggregate values
            Daniel Teo

            You probably want to use level-of-detail calculations (LOD).


            There are 2 options to write your LOD in this case -- using FIXED or INCLUDE:

            {FIXED [Indicator]: AVG(Score)}

            {INCLUDE [Indicator]: AVG(Score)}


            The LOD calculations averages the score per indicator first, and then applies an (unweighted) average on that average score. In this example, the weighted average for Standard A is 4.857, but the average of (3.000 + 5.600 + 6.250) is 4.950.

            In this simple example, both calculated fields give you the same result. Which one you want to use depends on how you want it to behave with regards to your views and filters.

            - Fixed ignores the other dimensions in your views and is calculated before any dimension filters are applied.

            - Include is calculated in addition to whatever dimensions are in the view and after any dimension filters are applied.


            Here's the online help page on LODs:

            Overview: Level of Detail Expressions


            And this blog post helped me a lot in understanding on how LODs work and how to write them. Definitely worth a read!

            Top 15 LOD Expressions | Tableau Software

            2 of 2 people found this helpful
            • 3. Re: Aggregating aggregate values
              Chris Lozier

              Thanks so much, Daniel. Above and beyond!

              • 4. Re: Aggregating aggregate values
                Chris Lozier

                Daniel - LODs open a whole new world, but now, of course, I have followup challenges. I've fleshed out your mock data/twbx to more accurately reflect the real data. Lengthier data hierarchy: questions > indicators > goals > standards > competencies. And then I have multiple Programs administering the tool. I guess my latest challenge arises because once I apply the LOD to be fixed on one of the hierarchical dimensions, Tableau no longer distinguishes between programs. For example, in the image here and in the twbx, you can see that the Avg Score per Indicator for Indicator A is 2.417 for Programs X, Y, and Z. I want that to be 2.5, 2.75, and 2, respectively.


                Can I fix more than one dimension in an LOD or, better yet, can i filter LOD calculations?




                LOD image 1.jpg

                • 5. Re: Aggregating aggregate values
                  Daniel Teo

                  Great to see you're getting excited about LOD expressions, Chris! They are really great to use!


                  As for your question, that's where you want to use the INCLUDE function instead of FIXED. Fixed ignores the filters and dimensions in your view, meaning it will calculate the average per question across all dimensions (I see you changed the fixed calculation to use the Question dimension). You will also see for instance that question 29 has a value of 1.667, since it calculates the average of question 29 across all dimensions.


                  Using the INCLUDE gives you the correct average per indicator. INCLUDE works within the context of the dimensions in your view.


                  However, you may have noticed another problem. Since you have the Question dimension in your view, you still get the weighted average on a Goal level, e.g. Program X - Goal 1 now has an average of 2.6 instead of (3.0 + 2.5) / 2 = 2.75.


                  So what you want in addition to your INCLUDE statement, is to also EXCLUDE the question dimension:

                  {EXCLUDE [Question]: AVG( {INCLUDE [Indicator]: AVG(Score)} )}

                  Now the new measure gives you an average of 2.75.


                  Of course, if you do not intend to show Question in your view, you don't necessarily need to bother with the EXCLUDE. The INCLUDE will then give you the correct results:


                  Don't worry if it's a bit confusing right now. LOD expressions can be a bit tricky to work with at first, and to be honest it is still a bit of trial and error for me as well. I'd also suggest experimenting with the different options step-by-step, instead of trying to solve it all in one go

                  1 of 1 people found this helpful