Calculating Averages when using a Calculate and Filter Measure

[MarkDGaal]

Hello, I'm having a little trouble figure out what is the best way to express my average over the course of 3 years using Measures.

My data structure looks like this (Table 1). As a note, there are other more unique data-elements (not illustrated below) that won't allow for this Table to be summerized/grouped to a higher level.

FullName  | Hours Worked | Location 1 | Location 2 | Labor Category  | Fiscal Year
John Smith| 10           | ABC        | ABC        | Worker          |  2015
John Smith| 5            | XYZ        | XYZ        | Manger          |  2014
John Smith| 7            | XYZ        | XYZ        | Manger          |  2015
John Smith| 3            | ABC        | CDA        | Worker          |  2013
John Smith| 2            | XYZ        | XYZ        | Manger          |  2015
John Smith| 10           | ABC        | ABC        | Worker          |  2015
John Smith| 5            | XYZ        | XYZ        | Manger          |  2014
John Smith| 7            | XYZ        | XYZ        | Manger          |  2015
John Smith| 3            | ABC        | CDA        | Worker          |  2013
John Smith| 2            | XYZ        | XYZ        | Manger          |  2015

My Measure looks like this:

Avg Hrs per Person = CALCULATE(SUM(Table1[Hours Worked])/DISTINCTCOUNT(Table1[FullName]
), Filter(Table1, [FullName]<>"No Matching Name" && [Location 1]=[Location 2] && ([Labor Category] = "Worker"|| [Labor Category]="Manager")))

Because John Smith was an employee for 3 years and his name remained the same the distinct count of John Smith is always 1 not 3. Now the problem is that I want this measure to represent the average of 3 year's of averages for John Smith; aka an average of an avearage.

I believe that the solution here is to use some combination of AVERAGEX, VALUES, and/or SUMMARIZE but the solution is illuding me.

I've tried creating 3 separate measures (one for each Fiscal Year) but I don't see and easy way to average the result of 3 separate measrues. Also, I've been reading a bit on how Averaging Averages is not the best course of action here, kindly advise if there is a better route. Thanks in advance and let me know if you need any more detail.

[Anonymous · Accepted solution]

Assuming your current measure is working as you want, the solution is just

Yearly Average := AVERAGEX( VALUES(Calendar[Year]), [Avg Hrs Per Person])

All the X routines work the same way... "Iterate over elements in the first parameter, evaluate the 2nd expression for each... then aggregate the results together".

[MarkDGaal]

Keep it simple I suppose, I kept trying to identify the year value in the Values funciton. (I'm new to DAX) 

Quick question why can't I just put the acutal expression from the [Avg Hrs Per Person] Measure inside of AVERAGEX? Or more simply.... why the need for the "intermediate" measure....?

[Anonymous]

That should work fine as well, except you will need to wrap it in a CALCULATE( ).  Calling a measure has an "implicit calculate".

This call to CALCULATE is a bit fringe in knowledge -- it is to convert the Row Context (created by the X function) to a Filter Context.

[MarkDGaal]

So you are saying this should work?

AVERAGEX(VALUES(Table1[Fiscal Year]), CALCULATE(SUM(Table1[Hours Worked])/DISTINCTCOUNT(Table1[FullName]
), Filter(Table1, [FullName]<>"No Matching Name" && [Location 1]=[Location 2] && ([Labor Category] = "Worker"|| [Labor Category]="Manager"))))

[Anonymous]

Let's just say... that if it doesn't... only @marcorusso has a chance of telling us why ... 🙂

[MarkDGaal]

It does not evaluate to the same average as having the Measure in the AVERAGEX function. I'll defer to @marcorusso as well.

[marcorusso]

If you have:

M := <exp>

X := aggX ( <table>, [M] )

You are doing:

X := aggX ( <table>, CALCULATE ( <exp> ) )

In your case, you forgot the CALCULATE embedding your measure. In this way, context transition will affect also filter arguments (the FILTER inside CALCULATE).

You should use this:

=
AVERAGEX (
    VALUES ( Table1[Fiscal Year] ),
    CALCULATE (
        CALCULATE (
            SUM ( Table1[Hours Worked] ) / DISTINCTCOUNT ( Table1[FullName] ),
            FILTER (
                Table1,
                [FullName] <> "No Matching Name"
                    && [Location 1] = [Location 2]
                    && ( [Labor Category] = "Worker"
                    || [Labor Category] = "Manager" )
            )
        )
    )
)

[MuhammadSiddiq]

Can anybody help me in calculating avrages of empoyees for a data of 5 years.

[Sean]

@MarkDGaal I can't believe THE @marcorusso responded!