Forum Discussion

Marmel's avatar
Marmel
Regular Visitor
8 years ago

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    ...
Need Help
v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution Sage
8 years ago

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's avatar
Marmel
Regular Visitor
8 years ago

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's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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])))

     

    Thanks,
    Xi Jin.

    • Marmel's avatar
      Marmel
      Regular Visitor
      8 years ago

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

      • Marmel's avatar
        Marmel
        Regular Visitor
        8 years ago

        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.