Data Story of the Month: Employee Sick Leave

 Happy February everyone!

Snow storms and record low temperatures are ravaging the northern hemisphere while those of us in the southern hemisphere are back in the grind of work and school after summer holidays. In both cases, sick leave is bound to see an increase.

Using Small Multiples to Visualize Sick Leave

Sick leave can cost your company, but so can ill employees. How is sick leave impacting your business across different departments? 

I recently worked with a client to help analyze employee sick leave, looking at metrics such as the average days sick leave per employee and it inspired me to use the preview feature for small multiples in Power BI to analyze some publicly available sick leave data. 

Data.gov.sg has some easy to use datasets spanning many years. I grabbed their data for 'Average Days of Sick Leave Taken By Employees' and analyzed it in Power BI.

Source: Average Days of Sick Leave Taken By Employees-Data.gov.sg

Step 1: Enable small multiples preview

Small multiples is a preview feature that was introduced in December, 2020. That means you have to enable it before you'll see the option to use it in your reports. It also means you can turn it off if it's causing you problems. 

To enable small multiples preview: 

1. Click File > Options and Settings > Options
2. Select Preview features
3. Tick the box next to 'Small multiples'
4. Click OK.
5. Restart Power BI desktop.

Step 2: Configure small multiples

Open Power BI and import your dataset (download the CSV from the link above to follow along with this example). 

Small multiples is currently available on the following visualizations:

• Stacked and clustered bar and column charts
• Line chart
• Area and Stacked Area charts

You'll notice the Small multiples section in the fields pane for any of these visuals now that you have enabled the preview feature. 

Simply drag the desired field into this box to quickly create multiple charts using the same x and y axis. 

By default, small multiples will display a 2x2 grid. As you resize the visual, the charts within it will resize so that you can always see 4 multiples in one view. Use the scrollbar to view others if you have more than 4 small multiples.

If you want to view your small multiples in something other than a 2x2 grid, this can be changed in the Grid Layout section of the formatting for the visual:

Step 3: Visual Interactions

Small multiples behave like their own visualizations if you select an individual data point. However, you have the added benefit of treating them as a group. To do this, click on an item in the legend or a label in the x axis. Note how the cross filtering of other visuals changes: 

Using DAX to Analyze Sick Leave

The dataset from Data.gov.sg was nicely formatted and already had averages calculated for us. How can we use DAX to get these average calculations? 

For this demonstration I'm going to use a very simple dataset. Averages are tricky enough, so we'll stick with just a few numbers. 

Excel with Allison Sick Leave Data

I have created a sample dataset with a very small number of fictitious employees and recorded their sick leave over the past year. The dataset is very simple and contains two tables: 

• DimEmployee
• FactSickLeave

I want to know the total number of sick leave days per employee, and the average across the department. 

We can calculate this average over the ALL employees, or ABSENT employees. We'll call these 'Employees' and 'Absentees' to align with the data from Data.gov.sg used above. 

Total Sick Leave Days

Let's start with the easy measure:

Total Sick Leave Days = COUNT(SickLeave[Date])+0

Note I have added +0 to this measure because Chad, Ethyl and Flynn are healthy employees who have never taken sick leave. I want them to help bring the average down when I calculated average sick leave by department, therefore they must get credit for 0 sick leave days. We know this information is zero, not null, so it makes sense to add +0 here.

Average Sick Leave Days

Now we're ready to calculate Averages. Our report will look something like the below when we're done:

Once we have correctly calculated the Total Sick Leave Days, we can easily get the average per employee. We simply need to recall our definition of Employee = every employee in the company. In our dataset, this is everyone in the DimEmployee table. So, we will average the [Total Sick Leave Days] over the DimEmployee table: 

Avg Sick Leave Days per Employee =

AVERAGEX(

DimEmployee,

[Total Sick Leave Days]

)

But this average seems a bit low. We know that Ali took 6 days sick leave and everyone who took sick leave took at least 2 days. How can the average be less than 2? Because we are looking at ALL employees, and Chad, Ethyl and Flynn bring the average down with their perfect attendance records. 

If we want to calculate the average per absentees, we must again define Absentee = employees with more than 0 sick leave days. So, instead of calculating the average over the entire DimEmployee table, we will simply wrap it in a FILTER to filter only those employees with [Total Sick Leave Days] > 0. 

Avg Sick Leave Days Per Absentee =

AVERAGEX(

FILTER(DimEmployee, [Total Sick Leave Days]>0),

[Total Sick Leave Days]

)

You can verify these numbers using the Total calculations provided in the table above. 

Now that you have seen how to perform these calculations on a small sample dataset, try using your own data to get the same results.