I am not sure if I understand completely what you are trying to display....
I think you will need to make two charts - one that shows COUNT of individuals with the same score, the other that shows a SUM of individuals with scores within a certain range.
You can easily add filters to select the names of person in charge when displaying the data on your charts.
You can then create a dashboard that shows the two charts side by side with filters/parameters to select.
Hope this helps. If you like, you can post dummy data.
Actually, I've done what you've suggested, but I was hoping for a more elegant/compact solution. In other words, I'd like to convert what I have (the previously attached picture), to something like what I have in the last tab of the packaged dummy workbook.
Dummy_quad.twbx 47.3 KB
Not sure if I quite got there, but maybe this can be a first step.
I think you can accomplish Plot1 by making a window calculation:
and placing it on the Size shelf.
I also placed a copy of a Dimension duplicate version of Score1
and Total Score 2 on the Detail shelf.
For Plot 2, I similarly created window averages of Score1 and TotalScore2:
and again used the CountID to calculate the size.
I didn't verify the numbers.
211146Dummy_quad.twbx 66.5 KB
Thanks, Swaroop! This is what I was looking for. Now to get it to work in my actual data set ☺. Cheers!
Swaroop, First, many many thanks - I got this to work with your guidance. I do have one more question though: is there a way that I can have the CountID value also show up next to the circle? I realise hovering over shows the number, but some users would like to see the value right away in each quadrant.
Glad it worked for you.
With regards to the label, I did see that adding just a label wasn't quite working because
the label was the same color as the circle.
Instead, you could try a dual axis with text as a second label.
Because I used a white color, one problem will be if the circle
is too small for the label, then the label won't show up well.
But you could probably write a calculated field to add a set amount to shift the labels over,
or a calculated field to say that if the CountID is below a certain amount color the label black.
211146Dummy_quadB.twbx 67.8 KB
Ok, that makes sense, thanks! However, I have no idea how to do the shifting of the labels using a calculated field. Would that also help to have the numbers simply outside the circles (that was what my original plan was…basically have the number somewhere in the quadrant, but not in the circle; because some circles are vanishingly small)?
This doesn't look great, but here's the idea:
based on the quadrant and a bit to the x value
WHEN "Hi/Hi" THEN [MeanX]*1.1
WHEN "Hi/Lo" THEN [MeanX]*4
WHEN "Lo/Hi" THEN [MeanX]*1.1
WHEN "Lo/Lo" THEN [MeanX]*4
Or use a parameter to set the cross lines and
add/subtract off those reference lines:
WHEN "Hi/Hi" THEN [XRef]+1
WHEN "Hi/Lo" THEN [XRef]-1
WHEN "Lo/Hi" THEN [XRef]+1
WHEN "Lo/Lo" THEN [XRef]-1
211146Dummy_quadC.twbx 79.4 KB