How Do You Compare?
- Dawn Harrington
- May 29
- 2 min read
Have you ever encountered a data set where you have to compare a person's score with an average score? What kind of visualization would you use?
You could just do a simple bar chart with a Reference line indicating the person's score compared to the average score.

If you look at Andy Kriebel's Visual Vocabulary here. There are a couple of charts to use. Maybe you would use a paired bar chart so you can see the person's score compared to the average score.

But what about combining a lollipop with an Reference line? How do you create that?

Steps
For this chart I first placed the following fields on the Rows shelf.
User Name (It's hidden)
Business Name
Review Date - Exact Date - Discrete
I sorted the Review Date descending, in case the user had multiple reviews.
I changed the Chart type to Circle.
I placed the following calculated field on the Columns shelf.
Fixed Business Review Stars
{ Fixed [User - Name], [Business - Name]: AVG([Review - Stars])}
I changed this field to an average aggregation.

I also filtered the worksheet by User Name = Jennifer to speed it up during build.
I then placed Measure Values on the Columns shelf before the Fixed Business Review Stars calculated field.
I removed all of the measure values except for Fixed Business Review Stars and Review - Stars.

I made the chart a Dual Axis.
I changed the Measure Values marks card to a Line and placed Measure Names from Detail to Path.

I created the following calculated field and added it to Color on both of these Marks cards.
Circle Color
IF AVG([Fixed Business Review Stars]) > AVG([Review - Stars])
then "Yes"
Else "No"
END
I then changed Yes to red and No to blue.

I placed the Review Stars measure on the Measure Values card on Detail and changed it to an Average. I needed to do this step so the it will be picked up in the next step's menu.
Then from the Analytics tab, I added a Reference line per cell for Measure Values.
I then changed the Value from Measure Values to AVG(Review Stars). I made the below modifications to the view as well.

And that's it! I added labels for the circles. Added a border around the circles to make them sharp.
Feel free to check out the dashboard here.
Comments