(Note to self: Version 9.1 workbook.)
If I understand what you are looking to do, I think you should change the way your data is laid out in the data source.
Attached is an excel file. Sheet 1 is your original data. Sheet 2 is how I re-shaped it. I created a data source based on sheet 2, and I created Sheet 2 in your workbook using that new data source.
just from a quick look at your data it needs restructuring to work efficiently, is your source a file or server?
If file (excel/csv etc.) then you should select the columns for the peoples scores and pivot them so you have a row per person and 1 column for scores.
it was possbille to acheive what you wanted anyway, please see attached - although reshaped data would be better.
211581NK.twbx 18.0 KB
How much control over this data do you have? I think you'll have better results if you change the orientation a bit:
From here it was pretty simple:
- I created a bar graph, showing AVG(Score) on the Columns, and Student on the Rows
- So you can play around with the color, (and because no avg score was >50 in your example), I made a little parameter called Min. Passing Avg Score.
- We're almost there! I made a calculation to control the color:
- I also added a little reference line on the X axis so you can see where the Pass/Fail cutoff was
- Finally, I edited the colors to make sure GREEN was green and RED was red:
Finished product looks like this:
Please let me know if this works for you! Good Luck! --Michael
NITESHAA_TWBX Updated.twbx 30.7 KB
Thanks Everyone . I don't have control over data. It is complex calculation which is ending up in KPI which is nothing but percentage value. I think without reshape , it would be impossible.
I was thinking over creating three different bar graph for each of them in a different sheet and then bring them on a dashboard in a same container.What would be your recommendation?