1 Reply Latest reply on Feb 13, 2017 10:32 AM by Patrick A Van Der Hyde

    Weighted Average for Surveys

    Lang Sui

      I have a large spreadsheet where I would like to calculate the weighted average of activities performed by individuals (TUCASEID).  Below is a snap shot of the Tableau work sheet.

      TUCASEID = individual

      Tuactivity N = activity number

      Tufinlwgt = weight of individual (TUCASEID)

      Tuactdur = duration of activity in minutes

      Tutier1Code = activity code ( ie. 18 = travel, 12 = household chores)

       

       

      Scenario 1:

      Average hours per day people spend doing an activity for all people who participated in this activity: sum(tuactdur*tufinlwgt)/sum(tufinlwgt*Ia)

      Ia=1 if person spend time doing activity, and 0 if they did not

       

      I want to calculated the weighted average of hours per day the individuals travel (Tutier1Code = 18).  So in the image below (from what you can see), the equation would be [(20+30+20)*2022867.012324+(20*15+15)*11163308.9075+(2+10+1+6)*3196564.638743]/(2022867.012324+11163308.9075+3196564.638743)=227 min.

       

      If I wanted to calculated the weighted average of hours per day people spend doing household chores (TutierCode = 2), the equation would be [(45+90+90)*2022867.012324+(120+85+15)*11163308.9075]/(2022867.012324+11163308.9075)=220.8 min.  In this case TUCASEID 20050101050049 did not do any household chores and is not factored into the equation.

       

      Scenario 2:

      Average hours per day people spend doing an activity for all people: sum(tuactdur*tufinlwgt)/sum(tufinlwgt)

       

      So in the second example: people who spent time doing household chores based on population, the equation would be  [(45+90+90)*2022867.012324+(120+85+15)*11163308.9075]/(2022867.012324+11163308.9075+3196564.638743)=178 min.  In this case TUCASEID 20050101050049 did not do any household chores but is still factored into the equation to account for entire population.

       

      Overall I would like to visualize the data as bar charts, with colors that indicate different activities.

       

        • 1. Re: Weighted Average for Surveys
          Patrick A Van Der Hyde

          Hello Lang Sui,

           

          I think a Level of Detail calculation will assist with the first Scenerio.  The detail in the view is lower than the detail of the calculation so you will either need to utilize a Table Calculation or a LOD calculation. 

           

          Is it possible to share the workbook or portion of the workbook to assist with this? 

           

          In addition, you may wish to review the Data Revelations site for in-depth explanations of working with Survey Data in Tableau - www.datarevelations.com/category/visualizing-survey-data-and-likert-scales is a good place to start there.

           

          I hope that helps.

           

          Patrick