I've went totally old school here but I'm sure there are better ways to do this.
I think I would create a calculated field for each of your four dimensions (Purposeful activity, Resettlement, Respect and Safety). Create a case statement to convert the text (poor, not sufficiently good, insufficient progress, reasonably good and good) into relevant number 1,2,3,4,5. (assuming I have the order correct)
Then create a calculated field (total) to sum the four numbers (per prison) to get a single value between 4 and 20.
Assign (total) measure to dimensions Prison and Year and you should get a good KPI.
Colours 1-9 = Red, 10-14 = Amber and 15-20 = Green.
Please see attached workbook . is it close , what you are looking for ?
If so, I have pivoted your data . Please see below link about pivoting
Also, i have made year to discrete . it lets you assign different colors
Hope this helps
HMIP prison ratings_ari.twbx 89.8 KB
Alex, PIVOT is the answer!
Here's a very similar take to Ari's, but I've color-coded the Rating value rather than the Metric Name. You can reassign colors as you like.
I also dropped the values onto Shape, so you can assign a visual cue that isn't color-related (helps for accessibility requirements). You can change the assigned shape, or even add your own shapes to Tableau.
There is SO much you can do with this little tiny data set - that's what makes Tableau so much fun!
HMIP prison ratings.twbx 113.1 KB
Michael and Ari, two extremely helpful replies, thank you so much.
Michael, I feel like you were closest to what I was trying to achieve, although both solutions require the use of pivot, so I'll definitely look into that in some more detail for future reference.
Michael one slight issue, given that each report has four indicators, I would expect that all four indicators would show in the visual, but for some reason some of the prisons only show one or two indicators on a given year (and don't show an ellipsis after).
One other minor point, can I manually sort the Pivot Field Names rather than ascending/descending?
Thanks so much again.
Duh, what am I like, I always forget pivot.
I couldn't tell which one is Safety and which is Respect in your workbook.
I dropped your 'Pivot Field Name' onto colour and that made a lot more sense.
I think I would still go with an overall Total so that I just have one icon per prison. If there were 10-20 dimensions then it would get really busy and hard to read.
Some trial and error, and a bit of comparing Michael and Ari's solutions has meant that I found my own.
Michael, I noticed that Ari had also included Pivot Field Name within the marks as a colour, which I felt was a little confusing. However, by dragging it to the marks panel as a detail all of the indicators appear as they should and are helpfully colour coded.
I'm still not quite sure if it's possible to change the order of the Pivot Field Names or Pivot Field Values, but this is a great starting point. Thanks again everyone.
1 of 1 people found this helpful
You can change almost anything, starting with the "Pivot Field" column names. you can do that in the Data Source tab or in the workbook Measures and Dimensions panes - make the names something meaningful for the data set.
you can also control the order of the color values in the color legend: just drag and drop to rearrange them as you see fit.
Those 2 tips should get you closer to something that meets your requirements.
And a bit more playing around and I've now got them in the order that I want. Perfect!