none
Get MAX, AVERAGE, MIN for a calculated measure RRS feed

  • Question

  • 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):

    PivotTable1.png

    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):

    PivotTable2.png

    Fig 2 

    Thanks in advance for your assistance.




    Thursday, August 22, 2019 3:13 PM

All replies

  • Since in this specific use case this is going to be over the range of filtered jobs (by slicer or report filter) as well as by filtered phases for the Pivot Table I used the following:

    Avg Labor Hour / Actual Units:=
         AVERAGEX(
                  VALUES(JCDetail[Job]),
                  JCDetail[Labor Hour / Actual Units]
         )
    

    OR

    Avg Labor Hour / Actual Units:=
         AVERAGEX(
                  ALLSELECTED(JCDetail[Job]),
                  JCDetail[Labor Hour / Actual Units]
         )
    
    Right now this is giving me the same results.

    So I first used pivot table to gather the list of filtered jobs and filtered phases so I could see the Hours Per Unit (which above is called "Labor Hour / Actual Hours").

    I then used the Excel 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 so I get a single line showing the average and the DAX averagex command gave me a value of 1.403 which is different.

    The Min and Max function done the same way in DAX gave correct results.

    So now why does DAX averagex vs the Excel average of what should be the same list of values provides slightly different numbers?

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

    Thursday, August 22, 2019 6:40 PM