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

Re: Count workers with more than 80% billable hours

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.

Marmel Frequent Visitor
Frequent Visitor

Re: Count workers with more than 80% billable hours

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!

v-xjiin-msft Super Contributor
Super Contributor

Re: Count workers with more than 80% billable hours

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.

Marmel Frequent Visitor
Frequent Visitor

Re: Count workers with more than 80% billable hours

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

Marmel Frequent Visitor
Frequent Visitor

Re: Count workers with more than 80% billable hours

@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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 243 members 2,993 guests
Please welcome our newest community members: