Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I cannot seem to find a solution in previous posts:
My aim to to create a visual to show the capacity (in hours) a consultant can work vs the actual time he worked. To calculate the capacity per month, I have a staff data table which has each person's capacity per day, I then need to multiply this with the number of working days per given month, which I now have in my date table. Then I need to subtract any absences he/she moight have had, which I have in another table, as their absences affects their actual capacity. I have no idea how to go about this, as the staff table has only the emplyoee record once and not per month. Do I need to create a table to show per month and join it to my absence table?
In addition to this, my actual data is in another table with lots of complexities, but this data is per day and not per month. In the end I would like to display capacity (capacity hours/day * working days/month) - (Total absence hours per month) vs Actual time booked per month.
I hope someone can help me.
Column1 | FullName | Region | Capacity hours/day | ContractualHoursPerDay | Internal Rate | Archived |
1 | User1 | NL | 6.24 | 7.8 | €95.32 | FALSE |
1 | User2 | US | 4.8 | 6 | €110.8 | FALSE |
1 | User3 | China | 6.4 | 8 | $125.03 | FALSE |
StaffMember | Hours | Actual Hours | Capacity hours/day | ContractualHoursPerDay | Capacity per month (CC) | Total Hours | Absences.Total Absence Hours | Period_Year |
User1 | 3.5 | 3.5 | 6.24 | 7.8 | 129.28 | 7 | 8 | 112023 |
User1 | 0.5 | 0.5 | 6.24 | 7.8 | 135.52 | 0.5 | 8 | 82023 |
User1 | 0.5 | 0.5 | 6.24 | 7.8 | 143.52 | 0.5 | 0 | 32023 |
User2 | 6 | 6 | 6.24 | 7.8 | 116.8 | 6 | 3 | 42023 |
User2 | 1 | 3.5 | 6.24 | 7.8 | 129.28 | 7 | 2 | 112023 |
User2 | 5 | 0.5 | 6.24 | 7.8 | 135.52 | 0.5 | 5 | 82023 |
User3 | 4 | 0.5 | 6.24 | 7.8 | 143.52 | 0.5 | 2 | 32023 |
User | Period No. | Total Absence Hours | Period_Year |
User1 | 11 | 8 | 112023 |
User1 | 4 | 8 | 42023 |
User1 | 5 | 32 | 52023 |
User2 | 8 | 8 | 82023 |
User2 | 7 | 32 | 72023 |
User2 | 5 | 16 | 52023 |
User3 | 2 | 36 | 22023 |
User3 | 3 | 60 | 32023 |
User3 | 4 | 112 | 42023 |
I only want one value per staffmember per month for both capacity hourse per month and contractual hours per month.
instead of SUM() use MAX() for these values.
What is the difference between Hours, Actual Hours and Total Hours?
Here's how I would structure the data model
which then would make the computations trivial.
What is the expected outcome based on your sample data?
Hi,
Thank you for your reply. No, I don't beleive the calculations will not be necessary. I am trying to achieve a capacity/month number, which I can show in a column graph against Total Hours (which is the same as hours btw.). But to achieve the capacity per month number, I have to multiply with only working days and then subtract absences for that month. So:
User 1: Total Hours = 50
Capacity = (6.24 (capacity hours per day) * 21 (working days per month)) - 8 (absence hours for month) = 123.04 for said month.
My challenge is, how do I calculate this per month when each month has a unique networkingdays and absence amount. If I use AVERAGE or MAX in the way I did it now (Capacity hours/month being repeated in every row of transactional data), I cannnot show the sum of all employees's capacities/month vs total hours. Am I making sense?
What is the expected outcome based on your sample data?
Hi,
Please find the data attached. I have 3 different tables (of which the both is joined to the transactional data, because this is the only way I knew how, but I want to avoid it). I am calculating the capacity hours per month as follows in a calculated column:
read about GENERATESERIES.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
94 | |
92 | |
82 | |
69 |
User | Count |
---|---|
161 | |
129 | |
125 | |
108 | |
97 |