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.
***** Related Links *****
Power BI Calculated Column vs Measure: A Comparison
Calculated Columns Power BI: Tips & Best Practices
Measure Branching: Power BI Development Strategy
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
