Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
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])))
Thanks,
Xi Jin.
@v-xjiin-msftSorry I responded so late! I will try this and let you know how it works.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |