3 Replies Latest reply on Nov 1, 2014 9:16 PM by Aaron Clancy

    Weighted average on transformed survey data?

    alisa.mendelsohn.1

      Hello all,

       

      Dealing with survey data transformed into columns for Tableau. This means that each respondent is repeated many times over, and that different people are repeated different # of times (as they answered certain questions in the survey).

       

      I have a field for Weight, but can't figure out how to get Tableau to accurately calculate the Weighted Average only counting each person once. Please see the attached workbook. Greatly appreciate any help - thanks in advance.

       

      Alisa

        • 1. Re: Weighted average on transformed survey data?
          Aaron Clancy

          In order for Tableau to "only count each person once" on a set of transactional data like you have (record for each question for an ID)....you need to "Group" the data by ID.  That is what you're doing in your step 2 Goal Weight.  You're essentially saying: disregard region and question when computing the average.  When you average each repeating value within an ID you get 1 instance of that value.

           

           

          There are 2 different levels of aggregation needed for this type of question:

          1) avg the records for each ID    (A min or max would work here too since they're always the same value)

          2) avg the results of step 1

          Screen Shot 2014-10-11 at 1.58.37 AM.png

           

           

          You've already got 2.99 showing on page 2 by aggregating to only ID.  What is it you're looking to achieve?

          Do you need to present that 2.99 a different way or with different dimensions on the visual?

          No dimensions on the visual?

          Screen Shot 2014-10-11 at 2.03.38 AM.png

           

           

          Because of the requirement of 2 levels of aggregation, what you're looking for will most likely need to involve table calcs.

          • 2. Re: Weighted average on transformed survey data?
            alisa.mendelsohn.1

            Hi Aaron,

             

            Thanks for your help! I see what you mean about grouping by ID. Are you writing the SQL script in a table calc? i.e. How can I achieve this?

             

            Thanks again,

            Alisa

            • 3. Re: Weighted average on transformed survey data?
              Aaron Clancy

              Hi Alisa,

              To answer your question, no, I wasn't using SQL in a table calc.

              I was only using the language SQL to describe what is happening when you use a table calc.  The table calc I used in the included workbook should be sufficient.

               

              The concept is that you average the average [weight] of ID's.

              This is done with a calc like so:   window_avg(avg([Weight]))

               

              If you wrap a measure in avg it will take all of the instances of that measure within each instance of the lowest level of detail (ID) and average them.  Meaning if you have 5 ID's you'll have 5 resulting averages.

               

              The window_avg will take the 5 resulting averages and perform an average on them.  The output happens at all instances of an ID which causes repeating values:

              Screen Shot 2014-11-01 at 9.10.59 PM.png

               

              In your data you have 7 ID's.  This means you have 7 averages. A window_sum on the those averages results in the number 2.99.

               

              You only need to present this once so you can create a calc of: index()    place that on your filters shelf and set to true.

              This gives you one row with the value you need:

              Screen Shot 2014-11-01 at 9.14.40 PM.png

               

               



              measure for all ID's.  For presentation's sake, you only need one instance of 2.99 so you can use and index filter set to 1 and hide the header for ID