13 Replies Latest reply on Jun 7, 2012 9:53 AM by Jonathan Drummey

    Unweighted averages

    Jonathan Clark

      I am creating a Tableau workbook from responses to survey questions.  I want to have a bar graph that shows a selected company against an industry average.  The problem I have is that in the data one company has >70% of the total records so they are weighted too heavily in the industry average.  I need to find a way to average the responses by company and then average the sum of those averages for the industry average.

       

      Attached is an example of what I am doing with incorrect results.

       

      Thanks in advance for any help,

      Jonathan Clark

        • 1. Re: Unweighted averages
          Jonathan Clark

          I figured out a solution to my problem.


          If you create calculated fields for each company that calculates their averages and then sum those calculations up and divide by a count of the companies you get a correct industry average. 

           

          Thank you to everyone who read this and attempted to help.

           

          -Jonathan Clark

          • 2. Re: Unweighted averages
            Jonathan Clark

            The solution I developed only works if you have values for all the companies.  If any company doesn't have a value Tableau seems to treat an null as an unknown and the resulting sum is also null.  So the calculation doesn't work.

            • 3. Re: Unweighted averages
              Jonathan Drummey

              Hi Jonathan,

               

              One way around this is to use a WINDOW_COUNT() (or perhaps WINDOW_SUM()) instead of just a COUNT() to get the number of companies. The WINDOW_COUNT is a table calc that can act across all rows in the data, so as long as there is any response from a company on any question in the view then you can get an accurate count. It's also possible to set up a view that gets an accurate count of all companies across all responses in the data, but you need to use a different sort of question filter (one based on table calculations).

               

              The exact setting of the Compute Using will depend on what data is in the view, if you need more help please post another packaged workbook with the structure of the view that you want and I'll see what I can do with it.

               

              Cheers,

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: Unweighted averages
                Jonathan Clark

                Jonathan,

                 

                Thanks for the response.  I have actually found a way around the problem by using individual calculations for each company in the data to determine their percentage. 

                 

                Calculation for individual companies percentage: if(isnull(count(['companies responses']) / total(count(['companies responses']))),0,count(['companies responses']) / total(count(['companies responses'])))

                 

                I then average the individual calculations using the following:

                (['company 1 percentage'] + ['company 2 percentage'] + ['company 3 percentage']... ['company n percentage'])

                / total(countd([companyID]))

                 

                These calculations give me the correct numbers, but require manually creating a new calculation for each company and adding them to the industry average calculation.  I'd love to see a way to do this programatically, but I can't seem to come up with one.

                 

                Thanks again for the response,

                Jonathan Clark

                • 5. Re: Unweighted averages
                  Jonathan Drummey

                  Hi,

                   

                  A table calculation solution can work for an arbitrary number of companies, I've set it up in the attached workbook using a simpler solution than I'd originally proposed.

                   

                  The Individual Company % calc is the basic Percent of Total Quick Table Calculation, as you had created:

                   

                  IF ISNULL(SUM([Number of Records])/TOTAL(SUM([Number of Records]))) THEN

                      0

                  ELSE

                      SUM([Number of Records])/TOTAL(SUM([Number of Records]))

                  END

                   

                  This has its Compute Using set to Q2 (Annuity), so it calculates results for each company.

                   

                  Then there's an Average % calc that is:

                   

                  WINDOW_AVG([Individual Company %])

                   

                  This is a nested table calculation. When dragged into the view, it needs to have the Individual Company % Compute Using set to Q2 (Annuity), and the Average % Compute Using set to Survey Form Code, using the Calculated Field drop-down to select each in the Edit Table Calculation dialog: This setting just averages the individual company results for the question, and removes the need for doing WINDOW_COUNT. This way if a company answers one question but has

                   

                  screenshot1.jpg

                   

                  Finally, instead of using the parameter-based calc field as a filter (which wouldn't let all the data be available to the table calcs), I created a table calc Survey Form Code Filter with the following formula:

                   

                  LOOKUP(ATTR([Survey Form Code]),0)

                   

                  Put this on the Filter shelf and you can now filter for individual company results, while the rest of the calculations stay the same.

                   

                  One issue I can see with this solution is that depending on the setup of the pills in the view Tableau may or may not pad the data, so the first Individual Company % could fail to return 0 when there are no responses for a company. There are ways around that, but I'd need to get a better sense of what the view you want will look like.

                   

                  Jonathan

                  1 of 1 people found this helpful
                  • 6. Re: Unweighted averages
                    Jonathan Clark

                    Jonathan,

                     

                    Very nice solution.  I like it.  Unfortunately, it doesn't work for me because I need to use a parameter to select the current company. This is because we have a process in house that lets a user log in and pass a specific parameter to the dashboard to tell the dashboard who is logged in so that company can only see their data against the industry average.  Again, nice solution and I will definately file this away for future use.

                     

                    Thanks,

                    Jonathan Clark

                    • 7. Re: Unweighted averages
                      Jonathan Drummey

                      I think what I proposed could still work in the environment you describe, with one caveat. What you'd need to do is combine the parameter filter calc with the table calc filter calc to create a new calc with something like this:

                       

                      [Select a Company] == LOOKUP(ATTR([Survey Form Code]),0)

                       

                      The caveat is that since all the data is used to compute the view, you'd have to check whether Tableau Server would reveal the underlying data when the user downloads all data, I'm not sure whether that would be hidden or not by the filter.

                       

                      Jonathan

                      • 8. Re: Unweighted averages
                        Jonathan Clark

                        Jonathan,

                         

                        Thanks again for the response.  I figured out how your solution works (with some help from a Tableau rep) and, again, nice job. I was able to duplicate your solution in my data set.  At first I had a problem getting the average to come up correctly, but I realized I was using the compute using of the flag on the field itself as opposed to in the edit calculation screen.  Do you know why that would be?

                         

                        -Jonathan

                        • 9. Re: Unweighted averages
                          Jonathan Drummey

                          When you set the Default Compute Using in the Edit Calculation window, that only sets the compute using for *new* instances of the calc in the views. Any *existing* instances of the calc already in views (i.e. blue or green pills) don't have their Compute Using changed when the Default Compute Using is changed. Does that make sense?

                           

                          Jonathan

                          • 10. Re: Unweighted averages
                            Jonathan Clark

                            Not sure I follow.  One of my collegues is interested in this as well, so I ran this by him. 

                            Here is his comment

                             

                            "it sounds like the subset of data (the existing window) creates an instance with which the Table compute is utilized…the default compute using is only for new instances of the data.  I imagine this is either a hierarchy calculation issue or, as we suspect, a situation where the table compute is focused only on the dynaset created when you did the initial filter as opposed to the default compute which does the computation first on the entire dataset, then applies the filter"

                             

                            J Drummey…is this what is happening?"

                             

                             

                            If I'm following what you said, then the drop down menu "Compute Using" option on the pill itself only uses the existing data from the current view, whereas the Edit Calculation Compute using changes the query to the underlying data itself.  Is this what is happening?

                            • 11. Re: Unweighted averages
                              Jonathan Drummey

                              I'm not sure what your colleague is talking about in terms of filtering, and I didn't write my explanation clearly enough to say what happens when. I'll try to explain my understanding:

                               

                              Table calculations aren't instantiated in the Tableau data source the way non-aggregate and aggregate dimensions and measures are, they are computed by Tableau *after* it returns data from the data source. You can see this when you right-click on a data source and choose View Data: You won't see any table calc fields, but you will see other calculated fields. You will only see the results of the table calculations when they are instantiated in a view by dragging their pill(s) in. Then you can seem them when you right-click in the view and chooseView Data.

                               

                              Filters on non-aggregate and aggregate dimensions and measures are used in the query issued to the underlying data source for the view, whereas filters based on table calculations are applied after all data is returned and table calculations are computed. See this forum post for a good explanation: http://community.tableau.com/message/139603#139603

                               

                              I think of the Default Compute Using setting (the one you set in the Edit Calculation window that is mislabeled as "Default Table Calculation") as much like the Number Format or default Aggregation settings - it's a value attached to the calculated field that is applied when the calculated field is brought into a view, and can then be overriden from there. As I'd noted, the table calculation does not exist in the Tableau data source, so setting this Default Compute Using has no affect on the data source and doesn't change any calculations until the calculation is added to a view.

                               

                              When a table calculation is dragged into a view (worksheet), Tableau will apply the Default Compute Using to that calc and use that calculate the results. My understanding is that this the *only* time the Default Compute Using is applied. From then on, every instance of the table calc pill in the view is an independent entity with its own independent Compute Using that you can change without affecting other instances of the table calc.

                               

                              This makes sense in that if you've taken the time to choose your own Compute Using by clicking on the calculation's pill in the view, you don't want Tableau to override that as soon as you are bringing another instance of that calc into the view. Also, the terminology makes sense - in the Edit Calculation dialog you are setting the _Default_ Compute Using, not trying to apply that Compute Using to 1 or more views. To be clear, if you change the Default Compute Using, that only changes the compute using for new instances of the table calc, not any existing instances.

                               

                              So, here's what Tableau does (a simplified version) when generating a view:

                               

                              - Pulls data from the data source using the dimensions in the view and non-table calc filters.

                              - Performs table calculations, using each table calc's Compute Using settings.

                              - Applies the table calc filters.

                              - Generates the displayed view.

                               

                              A few more points while I'm thinking about this:

                               

                              - The "default Default" Compute Using is Table (Across) if there are dimensions on Columns or Columns and Rows, Table (Down) if there are only dimensions on Rows.

                               

                              - If you have *not* set the Default Compute Using and drag a table calc into a view that already has an instance (or copy a table calc within a view by Ctrl+dragging it), the new instance of the table calc will inherit the settings of the existing table calc. It seems like this convenience behavior gets a little flaky at times with nested table calcs, so I generally walk through and make sure the settings are all what I need.

                               

                              - If you have set a Default Compute Using and the required dimension(s) are not in the view, the calculation will fail, the view will not refresh, and the table calculation pill will have a red exclamation point icon on it.

                               

                              - Based on the issues described in the prior two paragraphs, I generally will specify the desired compute using as a comment in the calculated field's formula so I can refer to it later.

                               

                              Does this make sense?

                               

                              Jonathan

                              1 of 1 people found this helpful
                              • 12. Re: Unweighted averages
                                Jonathan Clark

                                Jonathan,

                                 

                                Thank you for the detailed description.  I think I understand now how it works and appreciate you taking the time to explain table calcs to me.  I have successfully implemented your solution in my work.

                                 

                                Thanks again,

                                Jonathan

                                • 13. Re: Unweighted averages
                                  Jonathan Drummey

                                  You're welcome!