Can I get help for calculating averages per day and per half-day combined ?

[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-juanli-msft]

Hi @Anonymous 

Create measures

total days =
CALCULATE (
    SUM ( Sheet4[duration] ),
    FILTER ( ALLEXCEPT ( Sheet4, Sheet4[Username] ), [is_day_off] = "no" )
)

discount days =
CALCULATE (
    DISTINCTCOUNT ( Sheet4[date] ),
    FILTER ( ALLEXCEPT ( Sheet4, Sheet4[Username] ), [is_day_off] = "no" )
)

half day =
0.5
    * CALCULATE (
        DISTINCTCOUNT ( Sheet4[date] ),
        FILTER (
            ALLEXCEPT ( Sheet4, Sheet4[Username] ),
            [is_day_off] = "yes"
                && [Activity] = "half day off"
        )
    )

final = [total days]/([discount days]-[half day])

Best Reagrds

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-juanli-msft ,

Thank you for the suggestion.

This seems to work just fine, after a couple of modifications to adapt it to my data.

However the only downside is that this average is not affected by a filter, such as a filter on the date.

It always displays the average over the entire dataset. If I would want to see the average hours per day of a user for a specific week or month for instance, I couldn't see it with this method.

Is there a way to circumvent this ?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Modify the measure as below

total days =
CALCULATE (
    SUM ( Sheet4[duration] ),
    FILTER (
        ALLSELECTED ( Sheet4 ),
        Sheet4[Username] = MAX ( Sheet4[Username] )
            && [is_day_off] = "no"
    )
)


discount days =
CALCULATE (
    DISTINCTCOUNT ( Sheet4[date] ),
    FILTER (
        ALLSELECTED ( Sheet4 ),
        Sheet4[Username] = MAX ( Sheet4[Username] )
            && [is_day_off] = "no"
    )
)


half day =
0.5
    * CALCULATE (
        DISTINCTCOUNT ( Sheet4[date] ),
        FILTER (
            ALLSELECTED ( Sheet4 ),
            Sheet4[Username] = MAX ( Sheet4[Username] )
                && [is_day_off] = "yes"
                && [Activity] = "half day off"
        )
    )


final = [total days]/([discount days]-[half day])

If you add columns from "Date" table to the slicers, please create relationships between data table(Sheet4 in my example) and "Date" table.

Best Regards

Maggie