7 Replies Latest reply on Jun 24, 2016 12:20 PM by Christopher Dorosky

    How to display Both the average and the count in the same table

    Christopher Dorosky

      I've got some data that I'd like to display both the averages and the count for.

       

      For instance, there are 50 People taking a survey. Their names are saved in a Dimension "Raters"

      They are taste testing several products. These products are saved in a Dimension "Products"

      They answer 4 questions. Taste, Texture, Appearance, Uniqueness, all saved in Dimension "Question"

      The actual ratings are saved in "Ratings". This is a measure.

       

      I can very easily make a table with Raters on the Rows, Question on the Columns, AVG(Ratings) in the text.

       

      This shows me the average score for each question the rater answered.

       

      It looks like this:

      RaterTasteTextureAppearanceUniqueness
      Joe2.24.33.72.4
      Bob3.01.23.44.4
      Sally4.53.34.53.2
      Jessica5.03.02.01.0

       

      So far, so good.

       

      Jessica's results look suspiciously integerish. When I look at the background data, I see that she only answered for 1 product.

      I'd like to be able to add a column to the right of uniqueness (or Rater, whatever) which is the count of all responses for that person. I don't care if it is the count of products answered, or questions answered (nominally products answered *4).

       

      I've played with this quite a bit, and I'm not sure that it is possible.

       

      If I wanted to filter the table based on the averages - for example - don't let anything in with an average below 2.0, I can do this.

      I can't do it for the count, because I'm aggregating a value that is already aggregated.

       

      I'm stumped. How can I display, then hopefully filter out, raters who didn't respond to enough products?

      Create a set from an entirely different table in another worksheet?

       

      Thanks,

       

      Chris

        • 1. Re: How to display Both the average and the count in the same table
          Christopher Dorosky

          I have another very similar issue, same example.

           

          Suppose I want to create a variable that I can use for coloring, which is scaled based on the number of values (averages)  that are greater than 2.5.

           

          Can this be done?

          • 2. Re: How to display Both the average and the count in the same table
            Deepak Rai

            I think for your second question try doing following:

            Create the view using your value (Averages) and then use Conditional formatting  to Color Averages:

            For Example:

            IF [Avg] > 2.5 THEN "GREEN" ELSE "RED" END

            Hope it Helps.

            Thanks

            • 3. Re: How to display Both the average and the count in the same table
              Christopher Dorosky

              Deepak,

               

              Thanks for the suggestion, but that doesn't do what I want. 

              The coloring was probably a bad example - I need a continuous scale.

               

              The major problem results from not being able to do aggregations of aggregations. I'm looking into LOD now, but it does not appear to be able to do what I want.

               

              Simply put, I have values in the raw data. Those values become averages. I want to count the averages that are greater than X. This seems impossible in Tableau (due to the no aggregate of aggregates rule), but trivial in Excel.

               

              Is Tableau really this limited, or am I fundamentally doing something wrong?

               

              Does the answer lie in LOD?

               

              Thanks,

               

              Chris

              • 4. Re: How to display Both the average and the count in the same table
                Simon Runc

                So first thing to say is that Tableau and Excel are very different tools for different jobs, and 'think' in fundamentally different ways...below is an slide from my training deck on the subject

                 

                This is why Tableau can run queries over 100+ Million rows in seconds, and Excel struggles with a few 100 thousand. If you want to be able to piece together disparate calculations cells by cell, there is a performance cost.

                 

                Soap-box dismounted ...and on with the problem!

                 

                So if you want to perform aggregates of aggregates...Tableau offers 2 options, Table Calculations (which are dependent on the VizLoD), and FIXED LoDs which aren't (apart from certain situations). I'd suggest the FIXED LoDs in this case, else you'll need product in the VizLod to get the table calculation to work out the average over products, before aggregating those results. It might be helpful to think of FIXED LoDs are like SUMIFS in Excel (they are actually very different, but the effects are similar).

                 

                I've mocked up (what I think) your base data looks like (where I have a 1 or 2 product reviews for each person)

                 

                So to get a COUNTD of product reviews (per person), I've created the following calculation

                [Number of Products Rated per Name]

                {FIXED [Name]: COUNTD([Product])}

                 

                This has the same affect (if Excel had a COUNTD!) of this

                 

                FIXED LoDs run an aggregate, at the level specified (in this case Name), and return the result at row level (so can be used as a dimension) at the level specified (eg. as Bob has reviewed 2 items, the number 2 is returned to every row with Bob). I can make this a blue (discrete) pill, and so bring it in as a header (as per your requirements), or filter on it

                 

                For the second part, we take a similar approach

                [Average Rating per Name]

                {FIXED [Name], [Product]: AVG([Rating (1-5)])}

                 

                This is the same as

                 

                which when dragged down is

                 

                So we can now assess this field to see how many reviews are >2.5 (Again I've fixed this, on name, so we get back row level results)

                [Rating Count > 2.5 per person]

                {FIXED [Name]: SUM(IIF([Average Rating per Name]>2.5,1,0))}

                 

                giving us this (so Bob has 2 reviews >2.5, Jessica 1, Joe 0, and Sally 1)

                 

                You might need to play with the FIXED levels, as your data might be different from my guess! but hopefully this gives you the structure on how you can do these calculations in Tableau

                 

                Hope this helps (and makes sense), but if not please post back

                1 of 1 people found this helpful
                • 5. Re: How to display Both the average and the count in the same table
                  Christopher Dorosky

                  This got me what I needed. Thank you.

                  The other part was the realization that when you say FIXED, it is really is FIXED to just that.

                  I had data for different years. This was filtered out in the view, but when you use FIXED, it (rightly) doesn't pay attention to that. Oops.

                   

                  Is there a way to mark your answer as accepted?

                   

                  Thanks again.

                  • 6. Re: How to display Both the average and the count in the same table
                    Simon Runc

                    glad it all made sense...on the understanding of different calculation types and how the vizLoD 'reacts' to them you might find my answer to a question on Quora useful

                     

                    Answer - Quora

                     

                    You should see this at the bottom of each answer, so if you click this it will mark it correct.

                    • 7. Re: How to display Both the average and the count in the same table
                      Christopher Dorosky

                      Got it. When you view the thread from the inbox, that option isn't there. I had to go to the forum, find my own question, then do it.

                       

                      Thanks again, and I'll check out the quora link.