-
1. Re: Invidiual scores and Group average scores in one Chart
Mavis LiuJun 8, 2018 1:49 AM (in response to Sean Tobin)
Hi Sean,
It looks like you'll have to re-shape the data a bit.
So first pivot the data by highlighting all the measure columns and right click to select pivot:
You'll now see two new columns - Pivot Field Names and Pivot Field Values.
The Pivot Field names are contains the groups and Q period. Right click on the column and do a custom split based on the full stop to bring back the first and last columns:
Rename your columns as appropriate:
Now go into sheet one and we can start building the viz.
So we need Group and Period in the columns, then sum(value) and avg(value) in the rows:
Right click again and select Dual Axis.
Change one of the sum of values marks card into a bar:
and make sure the avg values marks type is circles. Then right click on the secondary axis and synchronise them, you can also hide the header afterwards (there's no need to have two axis which are the same):
Start changing the colours around, maybe also remove measure names from colour on the marks card.
Ultimately you should get this:
For my example in the above screenshot, I have used min(value) and avg(value). When I sum the values it seems way too high so it doesn't look right. Please let me know how this should be aggregated instead.
I've attached the workbook so that you can take a look.
Thanks,
Mavis
-
Pivotted values_v10.2.twbx 44.9 KB
-
-
2. Re: Invidiual scores and Group average scores in one Chart
Sean Tobin Jun 8, 2018 3:04 AM (in response to Mavis Liu)Hello Mavis,
Thank you very much for responding. I started down the data reshaping path but didn't get as far as you did. The workbook you attached is almost exactly what I am looking for. However, the last thing I needed is an average of the entire group broken out by quarter. I added a calculated field to the attached workbook with the changes I made. Thanks again!
-
exampleBook.twbx 44.7 KB
-