Search code examples
excelpivotpivot-tablepowerpivot

Excel Pivot table how to get average of a min field

I am trying to calculate average of a min field using pivot tables. I can do it in 2 steps right now but was wondering if its possible to do in 1 step using the pivot table. Right now I have to do a min pivot table and then copy paste that into another table and do another pivot on it. enter image description here

edit: I have uploaded sample here: https://easyupload.io/b6wvfd

enter image description here

Solution

  • Using DAX you could achieve it by using two measures:

    First calculate the Minimum of the items, Min of item:

    =MINX(Table1,Table1[age])

    Then do average of the minimum values, Avg of Min:

    =AVERAGEX(ALL(Table1[item]),
[Min of item])

    Result should be like this: enter image description here

    Setup the PowerPivot Table

    Transform your data into a table.

    We can now create the PowerPivot and add the data to the data model enter image description here

    Then create a new measure by clicking your pivot table and then "Measures" -> "New Measure..."

    enter image description here

    Fill in all the relevant data and start with creating the "Min of item" variable.

    enter image description here

    Then create the average of min (since we use the previous measure in this function):

    enter image description here