3 Replies Latest reply on Jun 8, 2018 8:18 AM by Michel Caissie

    Average of dimension types (Calculated value)

    Andrew Hutchinson

      Hi

       

      I am trying to create a calculated value that I can use to conditionally format the colour of the cells within a table in Tableau. I am able to create the calculated field to do this on a simple thing - for example a mean score is over or under a certain value. My issues is that I need to base it on the average score of the question - if the score belongs to a group of a dimension. Essentially my data is made up of a set of service types - service type 1,2,3,4,5 and 6 - and people are able to rate their satisfaction with the service they receive in a score from 1 - 10. The data is set up as follows:

       

      Service typeScore

       

      I need to be able to say if the average satisfaction score for service type 1 is 9.4 or over then the cell is green or if the average satisfaction score is 9.0 or over for service types 2,3,4,5 or 6 then colour the cell green.

       

      I was planning to use a calculated field:

       

      IF AVG([Satisfaction]) > 9.4 THEN "Good"

      ELSE "OK" END

       

      And colour based on "Good" and "OK"

       

      But couldn't work out how to add in the service type part - I had hoped to able to use a separate calculated field to do this - but i can't work out how to create an average of just service type 1 or just service types 2,3,4,5,6.

       

      I'd appreciate any help on this - I've searched for answers but it is difficult to find something that matches this query!

       

      I've attached an example workbook if that helps - version 10.5.3

       

      Thanks

      Andrew

        • 1. Re: Average of dimension types (Calculated value)
          Michel Caissie

          Andrew,

           

          Check in the attached if it is what you are looking for.

           

          First we will need the Score not only as a measure but also as a dimension, so I did the following calculation Score(ifNullThen0)

          ZN( [Score for service] )

          and convert it to a dimension. Basically, I create a dimension with Score for service and I replace the Nulls with 0.

           

          I did a view with the average for both  the Type of service  and   the  Type of service (group)

           

          Type of service:

          I get the average with

          MIN({FIXED [Type of service]: SUM([ScoreTotalValue])}) / MIN( [TotalNumberRecordsPerTypeOfService] )

           

          where [ScoreTotalValue] is

          {FIXED [Type of service],[Score (ifNullThen0)]: SUM( ZN( [Score for service] ) * [Number of Records] )}

           

          and [TotalNumberRecordsPerTypeOfService] is

          {FIXED [Type of service]: SUM( [Number of Records] )}

           

          Type of service (group) :

          I get the average with

          MIN({FIXED [Type of service]: SUM([ScoreTotalValue (group)])}) / MIN([TotalNumberRecordsPerTypeOfService (group)])

           

          where [ScoreTotalValue (group)] is

          {FIXED [Type of service (group)],[Score (ifNullThen0)]: SUM( ZN( [Score for service] ) * [Number of Records] )}

           

          and TotalNumberRecordsPerTypeOfService (group)] is

          {FIXED [Type of service (group)]: SUM( [Number of Records] )}

           

          You can see the result of those calculations in the ValidateData sheet. As you can see you get a value of the Avg for every columns.

          This is why I used a MIN in the AVG calculation.(you could use MIN,MAX or AVG).

           

          On the  view sheets, I used a dual-axis with a Bar mark, in order to be able to color the cells with the color logic.

          Be aware that I changed the numbers in the color logic  so I could display both colors.

           

          Michel

          • 2. Re: Average of dimension types (Calculated value)
            Andrew Hutchinson

            Hi Michel

             

            Thanks for your response - I was able to do this however I forgot to mention I would need it to be reactive to filters - so a weekly filter for example - when I tried filtering this doesn't change the calculated value - sorry I didn't include the week in the sample uploaded.

             

            Thanks

            Andrew

            • 3. Re: Average of dimension types (Calculated value)
              Michel Caissie

              Andrew,

               

              Try adding you filter to Context  (right-click the Date pill on the filter shelf and select   Add to Context).

               

              This way the filtering will occur before  the FIXED is computed.