Forum Discussion
DAX Measure Help
Thanks Anonymous . However it does not comply with the required definition:
sum of timesheet details billable hours of the resource in the month / (capacity of the month - sum of timesheet details hours of the resource in that month registered on project HIT012)
Further thoughts?
My mistake GuntherCoppens 🙂 So you want to calculate every single project's billable hours as a percent of the total capacity per month? And the total capacity is not allocated to each project? In that case I would create the following measures:
TotalCapacity = CALCULATE(
SUM('Capacity'[Hours])
)
BillableOfCap =
DIVIDE(
[TotalHoursBillable];
[TotalCapacity] - [TotalHours]
)
Drop these two measures in the matrix visualisation with time on one axis and projects on another, and the calculation should work.
[TotalHours] will calculate the number of hours in all timesheets across each project in the matrix.
[TotalHoursBillable] will calculate the same as [TotalHours], but filtered to only the billable hours
[TotalCapacity] will calculate the total capacity per month in the matrix. Since the Capacity table is not filtered by the timesheets tables, it will show the same values across all projects in the matrix.
[BillableOfCap] is simply a division of the above measures, where the denominator is [TotalCapacity] subtracted by [TotalHours] as per your specification.
Hope this helps!