cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wcameron14
Frequent 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

NameWorked Hrs.Available Hrs.UtilizationWorked Hrs.Available Hrs.UtilizationWorked Hrs.Available Hrs.Utilization
Bill Smith16016895.24%17517699.43%17318494.02%
5 REPLIES 5
JustJan Resolver IV
Resolver IV

Re: Monthly Totals in a Matrix

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 

 

Community Support
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?

How to Get Your Question Answered Quickly  
Regards,

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
wcameron14
Frequent 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.

Highlighted
Tad17 Solution Sage
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

wcameron14
Frequent 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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors