Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Marmel
Regular Visitor

Count workers with more than 80% billable hours

Hi!

 

I have these timesheets for our workers:

 

Project number    date                  hours        billable        Worker      Billable hours

12345                  01.01.2018         2,5                   1                 AK                2,5

12245                  01.01.2018          5                     0                AK                  0

 

There are several lines per worker per day in my table.

 

I have used columns to calculate numbers of hours that are billable and I have a measure for the percentage of billable hours:

 

Faktureringsgrad = CALCULATE(AlleTimerTabell[Sum billable hours]/AlleTimerTabell[Sum hours]*100)

 

I use a matrix to show the percentage of billable hours and conditional formatting to show who is over or under a given percentage for each month.

 

I want to count how many workers are over 80% for each month and I tried to do it by adding the worker to my values in addition to "faktureringsgrad" which is the percentage of billable time. I think I only get the count of those who have a total billable hours over 80% and not the count per month. While the conditional formatting seems to work the way I want, the count doesn't.

Is there any way I can get my count of workers with a billable percentage of hours over 80% per month?

 

Looking forward to any suggestions!

 

 

 

5 REPLIES 5
v-xjiin-msft
Solution Sage
Solution Sage

Hi @Marmel,

 

Could you please also share your pbix file with One Drive or Google Drive if possible? So that we can dig deeper and make some proper tests rather than just guessing.

 

Thanks,
Xi Jin.

Hi @v-xjiin-msft

 

I have made a little spreadsheet with some relevant data in it and two reports which illustrates my problem. I want to count the number of workers with a specific % billable time and see the change over several months. So I want to hide the measure of billable time and show only the count of workers over (or under) 80% for each month. The conditional formatting does it right, but the filter on workers doesn't.

 

As you can see, the count is related to total billable time%, and not monthly. 

 

https://drive.google.com/open?id=1CjynJWtHjRVXEXxuIawfFIDqwlzivg7C

 

Looking forward to your reply!

Hi @Marmel,

 

Maybe replace the Count of workers in Matrix with a new measure:

 

Workers num = CALCULATE(COUNT(dHours[Worker]),FILTER(dHours,dHours[Worker]=MAX(dHours[Worker])))

3.PNG

 

Thanks,
Xi Jin.

@v-xjiin-msftSorry I responded so late! I will try this and let you know how it works. 

@v-xjiin-msft 

Hi again!

 

Sorry. This is a good idea, but it doesn't solve my problem. Each worker has several timesheets and I guess you have just calculated the number of timesheets per month here. The correct count shoud be according to the number of lines with red colour in the above figure... So it is 2 in september, 1 in october and november and 2 in december. I think I need a formula to separate the billable% for each month.  I will continue to try to solve this, but I would appreciate any good ideas as DAX formulaes are new to me.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.