The world’s leading publication for data science, AI, and ML professionals.

Measuring Work-Life balance in Power BI

When I asked myself "How can I measure my work-life balance" I decided to use Power BI to answer this question. This is how I did it.

"What is the percentage of my time that I spend at work?". I posed this question to myself a few days ago. This article shows you how I approached this question and how I solved the challenge.

Photo by Austin Neill on Unsplash
Photo by Austin Neill on Unsplash

Introduction

A few days ago, I asked myself: What is the Percentage of my time at work?

Being a Power BI aficionado, I wanted to answer this question with Power BI.

This piece is like a Log, in which you can follow how I approached this question and how I solved it.

Questions to the data

First of all, what are precisely the questions?

Daily, I want to know the Percentage of work hours related to the 24 hours of the day.

Monthly, I want to know the average Percentage related to the hours of all days of each month. In addition, I want to calculate the relation of the sum of the work hours vs the sum of the hours per month.

I want to know the rate of my total working time vs the billable time on both levels.

As I have data only for the actual year, I don’t have to calculate anything at the year level.

I have two sets of data:

  • The list of all billable hours by customer
  • The list of work-hours without the distinction if billable or not

Prepare the Data Model

I loaded the two data sets into Power BI and added a Date table.

As I need to perform analysis on Time Data, I need a Time table.

I created a Time table with the following Attributes:

  • Time_Key A Key column with the format hhmmss, without leading zeroes

  • Time The time column

  • Hour The hour of the current time

  • Minute The minute of the current time

  • MilitaryTime The time in the format hhmm with leading zeroes

  • Hour_Dec The hour with the minute as a decimal

  • Hour_Frag The fraction of the hour per minute

  • Day_Frag The fraction of the day per minute

Here is an extract of the Time table:

Figure 1 - Extract of my Time table
Figure 1 – Extract of my Time table

The exact formulas on how I have calculated these number is beyond the scope of this article. Please leave a comment If you want to know the details, and I will write a description on this topic.

Tweaking the Data Model

I have to tweak my Data model:

Figure 2 - My Data Model
Figure 2 – My Data Model

Look at the Relationship between the tables Time_Min and Rapport 2021 (=Reporting 2021).

Because I want to use the Time_Min table as a Dimension- and as a Fact-table, I have to set the Relationship to Bidirectional-Filtering.

I suspect that I have to do the same with the other tables, as I want to combine the data in the tables "Rapport 2021" and "Soll-Ist" (= Target vs effective hours).

Note: The table- and column names are in German as my sources deliver data with german wording. I will do my best to translate everything into English

Simple Average

The first step is to see what is the result of a simple Average of My data.

As you can see in the following Screen-Shot, the result looks far from correct:

Figure 3 - First result with Average
Figure 3 – First result with Average

The Average will calculate the Average over all rows, regardless of the Granularity. The Average seems to produce the wrong results, but it points me to the approach I will follow for the next steps.

It makes no sense to calculate an average over several days. I need to get the number of days and divide it by the number of hours.

But, for one single day, the Average is correct.

Handling of different calculations

I need to evaluate the calculation level (Month or Day) and perform different calculations for these two levels.

I’ll do it with the following intermediary Measure:

Calendar Check =
SWITCH(TRUE()
    , HASONEVALUE('Date'[DateKey]), "Date"
    , HASONEVALUE('Date'[MonthKey]), "Month"
    , "Others"
)

The column MonthKey identifies each month for each year. For example, June 2021 is 202106

Now, I can use the result of this Measure in all other Measures and decide which calculation is the correct one.

Calculation the Part of Life Time

First, I want to calculate my work time’s rate vs my lifetime at the monthly level.

For example:

  1. The day has 24 hours
  2. I work for 8 hours in one day
  3. The part of work time is 8 / 24 = 33.3%

When I’m at the monthly level, I cannot use the Time_Min table, as it goes only up to 24 hours. But, I work more than 24 hours in one month.

So, I need to perform the following calculation:

Work Part per Month =
VAR SumOfHours = SUM('Rapport 2021'[Wertschöpfende Menge])
VAR HoursPerMonth = COUNTROWS('Date') * 24
VAR WorkPartPerMonth = DIVIDE(SumOfHours , HoursPerMonth)
RETURN
    WorkPartPerMonth

The Variable SumOfHours contains the sum of billable hours (= Wertschöpfende Menge).

The Variable HoursPerMonth counts the days in the actual period and multiplies it by 24 (hours).

To Consider only the Workdays, without the weekends, I used this for HoursPerMonth:

CALCULATE(COUNTROWS('Date')
    ,'Date'[IsWorkday] = TRUE()) * 24

The result of the calculation is stored in the Variable WorkPartPerMonth through a Division of SumOfHours by HoursPerMonth.

Second, I want to calculate the same rate at the daily level.

My data model allows me to summarize the actual working hours and the Day_Frag amount, as both are for one day and cannot exceed 24 hours.

Now I can calculate the sum of Day_Frag to get the correct result.

As the Filter context selects the matching rows in the Time_Min table for each day, I can calculate a Sum of Day_Frag to get the correct result.

Here is the Measure:

Part of Lifetime =
SWITCH(TRUE()
    ,[Calendar Check] = "Date"
        ,SUM('Time_Min'[Day_Frag])
    ,[Calendar Check] = "Month"
        ,[Work Part per Month]
    ,BLANK()
    )

The Measure [Work Part per Month] works and any other level as well. But, as my data covers only 2021, I consider only the daily and monthly levels.

Calculate relation between working and billable hours

Now I have to solve the last question: I want to know the rate of my total working time vs the billable time on both levels.

The Table Soll-Ist contains a list of hours per day in two columns:

  1. Soll: This is the number of hours I have to work per day (Target work hours)
  2. Ist: This is the number of hours I worked for a specific day (Effective work hours)

I started to think about how I can calculate this Measure.

My first thought was to use a LOOKUPVALUE() to get the Value from the Soll-Ist table according to the date in the Rapport 2021 table.

As explained in my other Article, Don’t start with optimized code. It may not be a good idea; I like to have simple and elegant solutions. I don’t consider LOOKUPVALUE to be a simple solution.

The second approach was to set the relationships to Bidirectional-Filtering. I don’t want to go this way, as this approach can have unwanted side effects,

In the end, I decided to add one more Relationship between the two tables:

Figure 4 - New Relationship between Rapport 2021 and Soll-Ist
Figure 4 – New Relationship between Rapport 2021 and Soll-Ist

Then, I created a New Measure to divide the two numbers:

Billable time vs Worktime = DIVIDE(
        SUM('Rapport 2021'[Wertschöpfende Menge])
        , SUM('Soll-Ist'[Ist Stunden])
    )

At first, I was surprised that it worked:

Figure 5 - Billable time vs Worktime
Figure 5 – Billable time vs Worktime

Then I realized that the Filter Context on both tables was set through the Date table. So I don’t need to do anything more to get the correct result.

Conclusion

I wrote this article during the development of the Report.

I expected more complex code in this example as time calculation can be tricky. Fortunately, this didn’t happen.

I think that this is due to a good data model.

It always makes sense to spend time thinking about a good data model to simplify your Dax code.

The Time_Min table has helped me calculate the results, even though I didn’t use all the columns in this table.

But the most significant benefit I had, was to clearly define the requirements and which question I wanted to answer. With the clarified requirements, I concentrated on what I need to do and not on what I liked to do.

Again, the time to clarify the requirements is worth it.

Here is the result:

Figure 6 - Result of the calculations
Figure 6 – Result of the calculations

As you can see, I spend between 17 % and 31 % of my time at work, and 75% of my work time is billable, which is not bad considering that I was on holiday during January and April and I had a three-day Training during April.

Unfortunately, I lost a customer at the start of July. Nevertheless, I still have 76.4% of billable work time.

I think I can be happy with these results.

Photo by Ian Stauffer on Unsplash
Photo by Ian Stauffer on Unsplash

If you appreciate my work, feel free to support me through

Salvatore Cagliari

Or scan this QR Code:

Any support is greatly appreciated.

Thank you.


Related Articles