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

# Average of dimension types (Calculated value)

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)

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)

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)

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.