Drill Down % Calculation Adjust to Context - Date/Calendar

Question

I am new to PowerBI. This could be over my head and am having a hard time wrapping my head around contexts/filters and how they work in conjunction with other nested filters. Anyway here is my issue.

I am trying to create a report that shows % of hours charged to one program compared to their overall hours a standard employee would work. I have created a company calendar that has all of the days in the year and if they are a workday or not. I have an 'Hours' column that has 8 in it if it is a workday, and 0 if it is a holiday or a weekend.

This is commonly known as an FTE or a Full Time Equivalent.

My issue is that when drilling down and up my totals are getting different values. This appears to be due to the context of the matrix table. When it is on 'Total' of the year it is using the full 2000 hours that a work year consists of, when really there has only been 1600 or so because it is only September.

To fix this I have tried to write the following formula but it isn't working quite as expected either. If there an easier way to fix this issue or what approach should I be taking. Cant seem to find an answer in other posts.

Basically when I am at the year 2018 level I want it to be 'Total 2018 Actual Hours/YTD 2018 Total Cal Hours'. When it is on Qtr I want it to be 'Total 2018 Qtr 1 Actual Hours/Total Qtr 1 2018 Cal Hours. When it is drilled down to Months I want it to be 'Total 2018 Month January Actual Hours/Total 2018 Month January Cal Hours'.

Thanks.

Table Examples

Cal Table

Date        Holiday  Hours
01/01/2018  Yes      8
01/02/2018  No       8
...
...

Hours Charged Table

EmpID    Name    Project   Date          Hours
1234     Bryan   123454.1  01/22/2018    2.3
1111     Steve   123456.1  01/15/2018    2.8
0156     Stu     123483.1  01/10/2018    2.8
...
...

Formula

FTE By Drill =
VAR Actual Hours = Sum(Timesheet[Hours])
VAR Cal Hours = Sum(Calendar[Hours])

      Actual Hours / IF(
                        ISFILTERED('Calendar'[Date].[year]),
                        TOTALYTD([Cal Hours],'Calendar'[Date]),
                        IF(
                            ISFILTERED('Calendar'[Date].[Quarter]),
                            TOTALQTD([Cal Hours],'Calendar'[Date]),
                            IF(
                                ISFILTERED('Calendar'[Date].[Month]),
                                TOTALMTD([Cal Hours],'Calendar'[Date])
                                ,0)
                        )
        )

Hila DG Hila DG · Accepted Answer · 2018-09-28T00:03:14

When you're using the VAR you're actually SETTING IT UP. Once you called it VAR it's not going to change, as it is not sensitive to context change. You're also using IF too much (you said you're new to DAX and these are 2 classic mistakes). What you're trying to do is getting the percent of Actual Hours. In DAX you'd get the measures using SUMMARIZE and then get the extra calculation of that.

So now you know how VAR works.

In DAX you work on TOTALS. If your DimDate table has a "has been" field you can use it, filter by YEAR 2018, and SUMMARIZE the HOURS of the month as well as the HOURS worked. Look at that table to see what's there. Now you can calculate the % worked.

A code snippet would be something alone the lines of:

SELECTCOLUMNS ("WorkPercent", WorkHous/ActualHous
SUMMARIZECOLUMNS (
FILTER ("WorkHours", [WORKHOURS], 
"ActualHours", [Actual hours worked], 
DimDate[Year] = 2018 && HasBeen = TRUE))

Drill Down % Calculation Adjust to Context - Date/Calendar

2 Answers