For the first WOW challenge of 2024, I set the task of completing a viz related to HR salary data.
Creating the calculations
To start, we’ll build out all the necessary calculations required, and display them in tabular form. First create
Employee
[Forename] + ‘ ‘ + [Surname]
then add Employee ID and Employee to Rows, along with Department and Job Title. Add Pay Level as a discrete dimension (blue pill) to Rows too, then format Salary to £ with 0 dp, and add to Text.
We want to compare each employee’s salary with the median salary of all the employees in the same pay level. For this we need
Median Salary per Pay Level
{FIXED [Pay Level]: MEDIAN([Salary])}
Add this to the view too. You should see that the value is the same for those rows where the Pay Level is the same.
We need to compute the difference between these numbers (to draw a line from the median to the salary)
Difference From Median
SUM([Salary])- SUM([Median Salary per Pay Level])
and we also need to understand the salary as a proportion of the median
Salary / Median %
SUM([Salary])/SUM([Median Salary per Pay Level])
format this to % with 0 dp and add both these fields to the table
Each employee is given a scale value based on the proportion
Salary Scale
IF [Salary / Median %] > 1.2 THEN 6
ELSEIF [Salary / Median %] >1.1 AND [Salary / Median %] <=1.2 THEN 5
ELSEIF [Salary / Median %] >1 AND [Salary / Median %] <= 1.1 THEN 4
ELSEIF [Salary / Median %] > 0.9 AND [Salary / Median %] <= 1 THEN 3
ELSEIF [Salary / Median %] > 0.8 AND [Salary / Median %] <=0.9 THEN 2
ELSE 1
END
Set this to be discrete, then add to the view on Rows.
Finally we need the markers for the 80%, 90% etc of the median salary
80% Median
0.8 * [Median Salary per Pay Level]
90% Median
0.9 * [Median Salary per Pay Level]
110% Median
1.1 * [Median Salary per Pay Level]
120% Median
1.2 * [Median Salary per Pay Level]
Building the Viz
On a new sheet, add Employee ID, Employee, Department and Job Title to Rows. Add Salary to Columns and set the mark type to Circle. Set the Colour of the circle to suit (or use #64cdcc).
Add Median Salary per Pay Level, 80% Median, 90% Median, 100% Median, 120% Median to the Detail shelf. Add Pay Level, Salary Scale and Salary / Median % to Tooltip.
Format the axis (right click) and set the scale to be at £k with 0dp
This will also change the formatting of the salary on the tooltip, but we want it to be more detailed so create
Tooltip-Salary
[Salary]
and format this to £ with 0 dp. Add this to Tooltip too and adjust tooltip to match.
Add a reference line to the axis (right click axis > add reference line) for the Median Salary per Pay Level. Set it to be per cell and display a dashed line with no labels/tooltips displaying.
Add another reference line. This time set it to be a band at the cell level which ranges from the 80% Median to the 90% Median. Don’t display any labels or tooltips or lines and fill the band with the appropriate colour.
Repeat the above process, adding a reference band from 90% – 110% of the median, and a further band from 110% – 120% of the median.
Add another instance of Salary to Columns. Change the mark type of the 2nd Salary marks card to gantt and adjust the colour. Add Difference From Median to Size and decrease the size as small as possible. Double click into the Difference From Median pill on the size shelf and manually type in * -1 to the end.
Now make the chart dual axis and synchronise the axis. Right click on the top axis and move marks to back.
We want the user to be able to filter the chart, so add Department, Pay Level (select All values) and Salary Scale to the Filter shelf.
Finally, format the chart – make each row a bit wider; set the background colour of the worksheet to #f9f8f7; remove column dividers; set row dividers to a thick white line; remove all gridlines, zero lines and axis rulers. Reduce the size of the axis values and increase the width of the initial 3 columns so the text doesn’t wrap as much. Test your filters.
If all working, then add the sheet to a dashboard.
Building the Legend
To build the legend, I simply duplicated the core sheet, then filtered to a specific Employee ID and hid the Employee ID, Employee, Department and Job Title fields. I then edited the reference lines to add custom labels to label each line band, formatting the text to display to the top or bottom. I added an annotation to the Salary circle mark to label that as ‘Employee Salary’ which I then manually moved into position.
When I added this to the dashboard, I then floated a blank object over the top so the legend could not be interacted with.
My published viz is here.
Happy vizzin’!
Donna
Donna + DataViz 