Excel : Sum of max in pivot table

Question

I would like to create a pivot table which displays the sum of a column depending on the time that person that can work on.

I have the following data in a sheet on Excel :

The workload is the time that a person expect working on a task in one week. The column AV represent the days that a person is working in one week.

KEY	Content Type	Week	Task	Name	Workload	AV
Week 01 : 09 oct - 13 oct * Person 1	Departement A	Week 01 : 09 oct - 13 oct	Task 1	Person 1	2	5
Week 01 : 09 oct - 13 oct * Person 2	Departement A	Week 01 : 09 oct - 13 oct	Task 1	Person 2	2	4
Week 01 : 09 oct - 13 oct * Person 3	Departement A	Week 01 : 09 oct - 13 oct	Task 1	Person 3	2	5
Week 01 : 09 oct - 13 oct * Person 1	Departement A	Week 01 : 09 oct - 13 oct	Task 2	Person 1	2	5
Week 01 : 09 oct - 13 oct * Person 2	Departement A	Week 01 : 09 oct - 13 oct	Task 2	Person 2	1	4
Week 01 : 09 oct - 13 oct * Person 3	Departement A	Week 01 : 09 oct - 13 oct	Task 2	Person 3	0	5
Week 01 : 09 oct - 13 oct * Person 1	Departement A	Week 01 : 09 oct - 13 oct	Task 3	Person 1	1	5
Week 01 : 09 oct - 13 oct * Person 2	Departement A	Week 01 : 09 oct - 13 oct	Task 3	Person 2	0	4
Week 01 : 09 oct - 13 oct * Person 3	Departement A	Week 01 : 09 oct - 13 oct	Task 3	Person 3	0	5

At the moment, this is the result that I get with a pivot table and the sum of availability (option : "sum of availability" in my pivot table.

				Workload	Availability
Departement A				8	28
	Week 01 : 09 oct - 13 oct			8	28
		Person 1		5	15
			Task 1	2	5
			Task 2	2	5
			Task 3	1	5
		Person 2		3	8
			Task 1	2	4
			Task 2	1	4
		Person 3		2	5
			Task 1	2	5

The result that I expect is :

				Workload	Availability
Departement A				8	14
	Week 01 : 09 oct - 13 oct			8	14
		Person 1		5	5
			Task 1	2	5
			Task 2	2	5
			Task 3	1	5
		Person 2		3	4
			Task 1	2	4
			Task 2	1	4
		Person 3		2	5
			Task 1	2	5

The difference between these two tables is that I have the sum of each person availability, in this example (5 + 4 + 5)

Is it a way to do it? If you need more informations, feel free to ask 😀

Thank in advance,

Fapinski.

Answer 1

Assuming that Availability is the same (or constant) per Name per week, then you can try the following:

1. Select your table and then Add to Data Model via Power Pivot tab in the ribbon. This should open up Power Pivot for Excel window with your table loaded in.
2. At the bottom-half, select any cell and create a Measure with:

Availability := 
  SUMX(
    SUMMARIZE('Table1', 'Table1'[Name], 'Table1'[Week], 'Table1'[AV]), 
    'Table1'[AV]
  )

3. Go back to the main Excel window, and now you can create a pivot based on the data model, using the new Availability measure.
   

Result: