Get MAX, AVERAGE, MIN for a calculated measure

[gsaunders]

Have a simple sample table holding Job, Date, ScopeOfWork, Employee, Units, Hours.

I have a simple calculated measures like:

Actual Units := SUM([Units]
Actual Hours := SUM([Hours]
Hours Per Unit:= DIVIDE([Actual Hours],[Actual Units],0)

What I need is to get the average, min and max of the [Hours Per Unit] measure.

Since those functions only allow a column and not a measure I am not sure how to get this value.

I haven't messed with Dax in quite a while so a bit rusty.

I simply want to know the average, min and max of that calculated measure in the current context. So whatever the user has filtered I want those to be calculated... just can't seem to get it to work.

Thanks in advance for your assistance.

[AlB]

Hi @gsaunders 

The question is a bit ambiguous. If the measures are run per employee, you could create a measure like this for the max. Place it in a card visual:

MeasureMax =
VAR AuxTable_ =
    ADDCOLUMNS ( DISTINCT ( Table1[Employee] ), "HperUnitCol", [Hours Per Unit] )
RETURN
    MAXX ( AuxTable_, [HPerUnitCol] )

and the same for MIN and AVERAGE using MINX and AVERAGEX 

[Anonymous]

I'm afraid you're not precise enough to give you any definite answer. When you say "average," you have to tell us what you want to average over. Same with the other measures...

Also, I suspect that you're not talking about measures but calculated columns. There is no such object like "a calculated measure."

Best
Darek

[gsaunders]

Darek, let me restate and hopefully clarify.

Have a simple table holding Job, Date, Phase, Employee, Units, Hours.

I have a simple measures (also called calculated field).  These are NOT Calculated Columns.

Actual Units := SUM([Units]
Actual Hours := SUM([Hours]
Hours Per Unit:= DIVIDE([Actual Hours],[Actual Units],0)

What I need is to get the average, min and max of the [Hours Per Unit] measure based on what the user does in the Pivot Table.

If this was just Excel with a column containing 10 values representing the Hours Per Unit I would simplye use:
AVERAGE(A1:A10)
MAX(A1:A10)

MIN(A1:A10)

But I am using Pivot Table against PowerPivot Data Model using DAX.

So for example the user may have one pivot table showing Job, Phase, Units, Hours, Hours Per Unit.  It would look like this: (Note they may have a slicer or filter on Job and / or Phase)

This is only filtered on Phase = 1 across filtered jobs in pivot table:

Job 100, Phase 1, 100 Units, 10 Hours, .1 Hours Per Unit

Job 200, Phase 1, 100 Units, 50 Hours, .5 Hours Per Unit

Job 300, Phase 1, 50 Units, 70 Hours, 1.4 Hours Per Unit

Job 400, Phase 1, 65 Units, 45 Hours, .69 Hours Per Unit

Here is an example image in Excel of Pivot Table (Some field names may differ in image):

Fig 1

I simply want to know the average (mean), min and max of that Hours Per Unit measure in the current context. So whatever the user has filtered I want those to be calculated... just can't seem to get it to work.

A main example of this in the pivot table would be the user would filter on Job and ScopeOfWork (only showing ScopeOfWork as row in pivot table) and then the Average(Mean) Hours Per Unit, Max Hours Per Unit and Min Hours Per Unit.  It would look like this:

Phase 1, Average Hours Per Unit = .6725, Max Hours Per Unit = 1.4, Min Hours Per Unit = .1

Here is an example image in Excel of Pivot Table (Some field names may differ in image):

Fig 2 

Thanks in advance for your assistance.

[Anonymous]

[Average HPU Per Job] =
    AVERAGEX(
        VALUES( T[Job] ),
        [Hours Per Unit]
    )

[gsaunders]

You are correct... the main use case is looking at this over the job, but it is possible they could slice other ways.

I used your code which does look correct to me.  But let me explain what I am seeing.

So I first used pivot table to gather the list of filtered jobs and filtered phases so I could see the all the rows showing Hours Per Unit for each job.

I then used the Excel (not DAX) Average function against those values and got 1.49.

Then I created second Pivot table filtering on same jobs and phases, but not displaying the job and phases in pivot table so I get a single line showing the average based on the DAX averagex command you provided and it gave me a value of 1.403 which is different.

The Min and Max function done the same way in DAX gave correct results as the equivalent Excel function provided for the same range of data.

So now why does DAX averagex vs the Excel average of what should be the same list of values provides slightly different numbers?   Having two identical pivot tables where one shows the Hours Per Unit for each job and then a second one that has same filters, but only displays the new DAX averagex function I would expect what excel's average function provided from the list in the first pivot table to match what is in DAX.

Side question... the [Average HPU Per Job] measure you created uses the values table function.  Is there a way to get the results of just values(JCDetail[Job],[Hours Per Unit) to display so you can actually see what it contains while in Excel?

Gotta get my mind back into the DAX world as it has been several years.

[Anonymous]

Now It looks like you want to average over jobs. This was the crucial piece of info missing.

Best
Darek