0

I have a dataset in Excel that I need to summarize. The source table is in a format like below:
Table example

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

Power Query example

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.

Improve this question
6
  • Are you looking for SUM of Maximum... or total number for each function for each month, quarter, year ? Max of each month or Quarter or Year is always single value ,,, so are you want to get SUM of MAx from each month or Quarter or year or ONLY COUNT?
    – Rajesh Sinha
    Commented Sep 12, 2019 at 6:39
  • Total for each function for each month (or other metadata values I have in the model) and allow the pivot table to group by year and qtr. For the example I've put in, Function 1 should show as 1.25, Function 2 as 1... and so on, I probably haven't put enough example data in. The measure I've got works for months, but when the pivot table groups by year it isn't showing an expected value.
    – monkeymagik
    Commented Sep 12, 2019 at 7:55
  • If you are looking for Total for each Function of each Month then use SUMIFS where one criteria is Function & other is Month and you may go for Quarter as well as Yearly also .
    – Rajesh Sinha
    Commented Sep 12, 2019 at 8:50
  • 1
    Since your sample data is improper so that I'm confused & unable to create solution for the issue!!
    – Rajesh Sinha
    Commented Sep 12, 2019 at 8:54
  • 1
    Please do not clarify your question in comments; edit your question to make it clearer and more complete.  Specifically, please put desired results into the question.
    – Scott - Слава Україні
    Commented Sep 13, 2019 at 1:37

1 Answer 1

Reset to default
0

In power query you can group by function and sum the FTE column to get the total FTE per function.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .