Monthly Totals in a Matrix

[wcameron14]

Hello!

I'm new to Power BI and am trying to work through somethig that seems simple but has me stumped.

I am trying to calcuate the utilization percentage by month by employee.

Utilization = Worked Hrs. / Available Hrs.

Available hours = 8* xx workdays in a month

I have created a field to assign 8 hours per work day and have tried a variety of ways to calculate a sum total of hours for each month. In the example below, I am trying to calculate the "Available Hours" column for each month. So far, when I calculate the hours I get 2088 for 2019, which is the total number of hours for the year. How do I get the Available hours to display as seen below?

                                     November 2019                    December 2019                               January 2020

Name	Worked Hrs.	Available Hrs.	Utilization	Worked Hrs.	Available Hrs.	Utilization	Worked Hrs.	Available Hrs.	Utilization
Bill Smith	160	168	95.24%	175	176	99.43%	173	184	94.02%

[v-shex-msft]

HI @wcameron14 ,

Since we are not so clear about your data structure, can you please provide more detailed information to help us clarify your scenario?

How to Get Your Question Answered Quickly  
Regards,

Xiaoxin Sheng

[wcameron14]

The data comes from a time tracking system. We import the data to Power BI to provide tracking on employee utilization on internal and extenal projects. There are calculations done in the time tracking system prior to exporting this data to Power BI. Our model then leverages both calculations and measures to create various metrics. 

The matrix in which I am working provides a view at the team, user(employee) and project view to show how many hours were worked by each employee on each project for each month.  "Available Hours" is currently provided by the system of record. In our dates table, we have a column called "Is Work Day" that serves as a binary indicator of whether or not the date is a work day. On the table where the employee hours are calculated, we only have the first day of the month in the date field (because we are only breaking down the data monthly). 

We are simplifying our utilization calculation and just want to use total work days * 8 hours per day. When I try to calculate this in Power BI right now I get "2088" for each month, which is the total number of work hours for the given year (my data set starts in 2018). I have also tried to use TOTALMTD but haven't been able to get to a correct answer.

[Tad17]

Hey @wcameron14 

Have you tried simply adding a timeline splicer to the dashboard/report to filter the dates down to just the month in question? Either using the default splicer or the much prefered one here: https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380786?tab=Overview

[wcameron14]

The way the matrix is presented it is expected to show a rolling 12 months and the available hours calculation shows the annual total for each month anyway, so adding a slicer wouldn't change that.

[JustJan]

Hi @wcameron14 

I assume that the calculation uses measures.

These would be pretty simple like:  AvailableHrs := sum('Date'[IsWorkingDay])*8. Again an assumption that you marked the working days in your date table. 

If you show this in you matrix where the "columns" of the matrix is your YearMonth AND the date table is properly linked to you data table then it should give you the right value for available hours.

If this doesn't help, some more details are required with the actual code, part of the datamodel, and the actual matrix definition.

Jan