I have a dataset in Excel that I need to summarize. The source table is in a format like below:
The data extends for years and includes a heap more metadata columns.
The value in each month is a value from 0 to 1 whether a staff member is fulltime or part time.
I'd like to summarize the data using pivot tables and an Excel data model over months, years and quarters to figure out how many people are in a particular role, function or location (or other values available).
I can create a basic pivot table and add months to columns in individually, but I'd like to have used some Power Pivot features to get more flexible pivot tables.
I've used PowerQuery to unpivot and format the data so I can use it in a pivot table from the Excel Data Model
To summarize this data, I've created a measure in PowerPivot to calculate the sum of the maximum values for each which is:
SUMX(VALUES[Role Title]), CALCULATE(MAX[FTE])))
The measure works OK for the months in the pivot table. However when the pivot table is grouped by year or quarter, I would like it to show the maximum value in that grouping, whereas the pivot table will recalculate over the lot.