Need solution to display rolled up values in a pivot table

[AT1]

I am using Excel 2016 and need a PowerPivot solution for the following situation.
I have a table with 5 columns. The names (and data types) of each column: Location (text), Name (text), Weekending (date), ShiftDate (date), TotalHours (int).
All dates in the Weekending column are Sundays which represents a rollup of all dates in the ShiftDate column.
For example, the Weekending column shows 10/6/19 for all corresponding ShiftDates between 9/30/19-10/6/19 and so on.

I would like to create the following pivot table:
Filter: Location
Rows: Name
Column: Min TotalHours of all Weekending weeks
Column: Max TotalHours of all Weekending weeks
Column: Avg TotalHours of all Weekending weeks

The issue I am having is that when using Min/Max/Avg, the pivot table is only able to recognize the values in the TotalHours column (which is based on ShiftDate) and I need the rolled up TotalHours based on the Weekending column.

Get and Transform works beautifully as a solution, but I need something in PowerPivot.

Thank you.

 

[RoryA]

It sounds like you need a measure to sum the hours, then create separate measures that are basically something like MINX(VALUES(Weekending),TotalHours) and so on.

 

[AT1]

Works perfectly! Thank you.

 

[RoryA]

Glad to help.