Using variables and HASONEVALUE() to calculate average in a matrix

[ngaray]

Hello everyone,

I need to create a matrix that calculates the average of values instead of row total. I see this is a common issue in this forum. I was able to find other posts that got me close to what I need, but I am still getting the wrong result.

In my example, I have a table that shows daily hours worked by employees (the lowest granularity is days). In the matrix, I would like to aggregate the hours by week. However, instead of calculating a grand total, I would like to calculate the average.

This is a snapshot of the raw data

 

This is how the matrix looks (with the incorrect "total"). The averages should be (7+15)/2 = 11 and (15+30)/2 = 22.5. Instead of 5.63 and 11.25

The DAX measure that I am using is 

Average Weekly Hours =
VAR AllWeeksAverage =
    AVERAGEX ( 'Employee Hours', AVERAGE ( 'Employee Hours'[Reported Hours] ) )
RETURN
    IF (
        HASONEVALUE ( 'Employee Hours'[Week of Year] ),
        SUM ( 'Employee Hours'[Reported Hours] ),
        AllWeeksAverage
    )

I believe variable AllWeeksAverage is what is causing the error. Something important to point out is that there can be weeks were an employee will not report any hours. In my example, John Doe didn't report any hours in week 3.

I am attaching the pbix file here

I appreciate your assistance,

Nathalia

[FrankAT · Accepted solution]

Hi @ngaray,

try the following measure:

Regards FrankAT

[ngaray]

Hi @FrankAT 

Your measure worked perfectly!

I only modified the aggregation in the true portion of the If condition from Average to Sum. I should have clarified that I want to see the total hours by week not the average.

I can see that my problem was that I didn't filter the table by employee.

Thank you for your assistance, it is very much appreciated!

Nathalia