# Power BI Timesheet & Wages DAX Solutions

This blog centers around a Power BI timesheet and the associated wage costs. I’m going to show you how to calculate time data and wage type using DAX. I’m going to demonstrate a DAX solution using measures and another one using calculated columns. You may watch the full video of this tutorial at the bottom of this blog.

This example came from a Problem of the Week challenge, which we run in Enterprise DNA. This is a real-life scenario that we often encounter in organizations and businesses, and Power BI is just the right tool to help make the job easier and faster.

So, let’s get to it.

## Power BI Timesheet & Wages: Calculated Columns Solution

For the calculated DAX solution using calculated columns, I go to the Time table and create a measure, which I call CC-Regular Hours, to calculate the regular hours.

In this formula, I get the hours from the table and put them into a variable (VAR). Then I calculate IF it’s a holiday, weekend, or a regular day. With the RELATED function, I leverage the data model to determine that, and then I use the two variables (_IsHolidayDay and _IsWeekendDay) to determine the third one (_IsRegularDay).

Finally, I use a SWITCH TRUE statement where I check for the regular hours. The other two (holidays and weekends) are going to be BLANK. And if it’s a regular day, I want to return the hours up to eight hours, and then I’ll use the RETURN Result construct.

I’ll do pretty much the same thing for the overtime hours formula (CC-Overtime Hours) – I will only change the SWITCH TRUE statement. This will return the hours for a weekend day. If it’s a regular day, it will return the hours above eight hours.

For the holiday hours, it will return all the hours if it’s a holiday day, and return blanks if it’s a weekend day or a regular one.

Now let’s move on to the wages. For the regular hours’ wage formula, I will leverage the calculated column I just made for regular hours. I am going to determine the appropriate wages by using the WageType, Straight Time.

I get the employeesâ€™ hourly wage from the data model and then I use a LOOKUPVALUE to get the multiplier from the disconnect Wage Types table to apply. And finally, I calculate the variable Result, then have the RETURN Result at the end.

For overtime, I’ll just change the wage type to Time and a Half.

I’ll do a similar thing for holiday wages, but I will just change the wage type to Double Time.

Finally, I’ll do a simple sum to make the total value available to me.

When I drag these columns into the report, we can then see the results.

## Power BI Timesheet & Wages: DAX Solution Using Measures

I’m going to do pretty much the same thing that I did for calculated columns, but this time I’m going to make one small change.

I don’t have access to the time value directly from the table, so I’m going to use a SELECTEDVALUE here to get the hours associated with the particular time entry that’s being displayed in the visual. Other than that, the code is the same as it was for the calculated columns version.

I’ve done a very similar thing for the overtime hours and the holiday hours. It’s the same rules as for calculated columns.

As for the wages, they are calculated very much in the same way. The only difference from the calculated columns is the variable for hours. I’m using the measure value that I calculated. I also do the same thing for the overtime wages and the same thing for the holiday wages.

Then, I will write a simple sum measure for the total wages.

And now I add these measures into the table so we can see the results. Notice that none of the measures is showing totals. To see the total values for the measures here, Iâ€™m going to use the fix incorrect totals pattern. To do this, I need to create measures that use virtual tables to mimic the physical table that I have here.

## Fix Incorrect Totals Pattern

To fix this, I need to create measures that use virtual tables to mimic the physical table that I have here. So for the total hours, I used SUMMARIZE construct to create a virtual table with a time ID and the hours. And then I use SUMX over the virtual table to calculate the value.

I’ll drag that into the table here below, and now we can see the total hours.

I’m going to do the same for the wages, where I’ll use ADDCOLUMNS and SUMMARIZE. And then for the result, I’m going to use the HASONEVALUE function for the measure that I already created and SUMX for the virtual table.

I then add that into the table here, and we can see that we have the total wages now.

Another thing is that when I place my Total Wages measure in a simple bar chart with departments and wage types, you’ll see that I don’t get any values here.

This is part of the fixed incorrect totals issue. And one of the best ways that I have found to fix this is to use the virtual table and change the measure.

So I’ll take out the one without the virtual table and I’ll add in the one with the virtual table.

## Conclusion

Both DAX solutions are great for this Power BI timesheet and wages challenge. However, they also have their pros and cons.

The benefits of using measures are that they are best practice. They are not calculated when the model is refreshed, but rather only when the measure is actually used in a visual. The downside for them is that it does take a little bit of extra DAX code when we’re working with virtual tables to represent things to get them properly calculated. But it is not a big thing at all.

As for calculated columns, I do find that they are easier to learn and visualize. They are a very familiar starting place for those coming from Excel. However, one of the limitations of calculated columns is that they are actually physically created in the data model and they do increase the size of your model, which will decrease the performance of your report.

And the general rule is that you should not create calculated columns on fact tables. If you have to create a calculated column, try to do it on dimension tables.

I personally use calculated columns to add categorization to some dimension tables, where there were many dimensions to be shown.

I hope you’ve enjoyed this one and learnt a lot. You can watch the full video of this tutorial down below.

Cheers!

Greg

## Setting Up A Dynamic StartDate And EndDate For Power Query Date Tables

I'll show you how to set up dynamic start dates and end dates using Power BIâ€™s power query date...

## Bookmarks In Power BI – Grouping by Report Page

I'm going to show you how I utilize bookmarks in Power BI. For example, if I have multiple bookmarks...

## Power Query Features: An Overview

This tutorial will discuss about the available features inside the Power Query Editor. You'll learn how...

## Creating A Master Reporting Planner For Power BI Deployment

In this tutorial, youâ€™ll learn how to create a Master Reporting Planner in the Analyst Hub to organize...

## Power BI Modulo and Integer-Divide DAX Functions

I thought itâ€™d be interesting to find a way to highlight functions and operators in Power BI that you...

## Evaluating Customer Margin Contraction Using Power BI

Here weâ€™re going to look at customer margin contraction. Weâ€™re going to try and work out what customer...

## Power Apps Basics: Development Environment And Running Your App

In this tutorial, I'll go through some Power Apps basics and walk you through how the development...

## Enhanced Data Visualization And Reporting Frameworks

Today, I'll talk about enhanced visualization frameworks for Power BI. This is the Analyst Hub, an...

## Automatically Find Your Top 3 Salespeople Per Region Using DAX In Power BI

There's so much you can do with DAX in Power BI, and one of these things is for you to be able to find...

## Bullet Charts: Advanced Custom Visuals for Power BI

In this tutorial, weâ€™ll discuss a custom visual called Bullet charts. They're mainly used for measuring...

## Best Power BI Reports Examples | Formula 1 Reporting Apps

For todayâ€™s tutorial, I want to take this opportunity to showcase and appreciate some of the best Power...

## Create Automated Lists Based On Ranking Calculations in Power BI

You might find a situation where across a range of different metrics you actually want to show the TopN...