DAX Measure Help

[GuntherCoppens]

I have the following snowflake model:

CDS Timesheet Line
Timesheet	Timesheet Line	Date	Project	Chargeable	Timesheet Key
TS1	10000	04.01.19	CDK005	Yes	TS110000
TS1	20000	05.01.19	HIT012	No	TS120000
TS1	30000	05.01.19	CDK005	Yes	TS130000
TS1	40000	06.01.19	HIT001	No	TS140000

Table: CDS Timesheet Details

Timesheet;Timesheet Line;Date;Hrs;Timesheet Key

TS1	10000	04.01.19	8	TS110000
TS1	20000	05.01.19	4	TS120000
TS1	30000	05.01.19	4	TS130000
TS1	40000	06.01.19	8	TS140000

CDS Date = Calendarauto() Date;Calendar Year;Calendar Month;CalendarYearMonth
04.01.19	2019	1	20191
05.01.19	2019	1	20191
06.01.19	2019	1	20191

Capacity

YearMonth;Days;Hours

20191	22	176
20192	20	160
20193	21	168
20194	18	144
20195	20	160
20196	22	176
20197	18	144
20198	20	160
20199	22	176
201910	21	168
201911	21	168

Trying to show table relations via the colors 🙂

I would like a measure to calculate the % billable of a given resource in a given month. I appreciate I did not specify the resource in the above, that sits in another table linked to CDS Timesheet Details, but I am assuming for now that this does not matter:

'= 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)

I can nicely create a measure like this:

Table: CDS Timesheet Details

TotalHol = SUMX(filter('CDS Timesheet Details';RELATED('CDS Timesheet Line'[Job_No])="HIT012");'CDS Timesheet Details'[Quantity])

But I keep struggling with the above mentioned measure: % billable of a given resource in a given month? Any thoughts

[Anonymous]

hi @GuntherCoppens ,

You can try to create the following measures:

TotalHours = CALCULATE(
     SUM( 'CDS Timesheet Details'[Hrs] )
)

TotalHoursBillable = CALCULATE(
     SUM( 'CDS Timesheet Details'[Hrs] );
     FILTER(
            'CDS Timesheet Line';
            'CDS Timesheet Line'[Chargeable] = "yes"
     )
)

Billable% = DIVIDE(
    [TotalHoursBillable];
    [TotalHours]
)

Then create a matrix visualisation with 'CDS Date'[month] as rows and 'CDS Timesheet Line'[Project] as columns. Drop the measures in the values field of the visualisation. This should produce the total hours, total billable hours and billable % per project per month.

[GuntherCoppens]

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?

[Anonymous]

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!