Need help with average per day functions to treat "half days" and "full days"

[Anonymous]

Hello,

I am building a report for my organization's timesheets. Users submit their hours through the system and I get access to all the data. I have a page per user displaying various information.

The problem I am facing is related to average worked hours per day.

My formula is quite simple:

average_hours_per_day = AVERAGEX( VALUES ('username'[date]), 'username'[sum_hours])

(username represents a user's table containing their timesheet submissions)

My problem comes from the fact that I exclude user's vacation days, days off for sickness etc from the average, as to not impact it with meaningless data. I have a simple filter that checks the category of each submission, and if it matches "holidays", "sick leave" it gets exluded (so there is no submission for that date).

This worked fine until I realized that it wouldn't work properly in the case of a user taking a half day off. The half day will be excluded but the submissions for the other half will be considered in the average. This will lower the user's average, because it is considered a full day in the formula above, even if the user only worked half of a normal day.

Here is some sample data to illustrate this:

Based on this data, the average worked hours for this user (excluding days off) would be:

(2+2 + 4 + 4 + 4 + 4 + 4 + 4) / 3.5 = 8 hours per day on average (3 days + 0.5 days)

But my formula considers it to be 4 days:

28/4 = 7 hours per day on average

Are there any functions that could help me here ? Or any other ideas ?

I have no idea how to fix this at the moment.

Thank you for reading.

Valentin

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

Based on your information, I have made a test.

Following your logic, you could try the formulas below.

Table =
SUMMARIZE (
    'Table1',
    'Table1'[Date],
    "hours", CALCULATE (
        SUM ( Table1[Duration] ),
        FILTER (
            Table1,
            'Table1'[Activity] = "Work"
                && 'Table1'[Date] = EARLIER ( 'Table1'[Date] )
        )
    )
)

Measure =
VAR a =
    CALCULATE ( SUM ( 'Table'[hours] ) )
RETURN
    IF ( a = 0, 0, IF ( AND ( a > 0, a < 8 ), 0.5, 1 ) )

Average work hours = 
CALCULATE(SUM('Table'[hours]))/SUMX('Table',[Measure])

Here is the result.

More details, please refer to my attachment.

Best  Regards,

Cherry