cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

## Monthly Totals in a Matrix

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%
5 REPLIES 5
Resolver IV

## Re: Monthly Totals in a Matrix

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

Community Support

## Re: Monthly Totals in a Matrix

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?

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Regular Visitor

## Re: Monthly Totals in a Matrix

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.

Solution Sage

## Re: Monthly Totals in a Matrix

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

Regular Visitor

## Re: Monthly Totals in a Matrix

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.

## Helpful resources

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors