Cumulative Pivot Chart

[Mr2017]

Hi

I need to create a cumulative pivot table chart.

I've created a simple example of what I'm trying to do using the 9 rows of data below:

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Client[/TD]
[TD="class: xl65, width: 64"]User[/TD]
[TD="class: xl65, width: 64"]Hours[/TD]
[TD="class: xl65, width: 64"]State[/TD]
[TD="class: xl65, width: 64"]Billable[/TD]
[TD="class: xl65, width: 64"]FY[/TD]
[TD="class: xl65, width: 64"]FYmonth[/TD]
[/TR]
[TR]
[TD="class: xl66"]CLAN[/TD]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]Approved[/TD]
[TD="class: xl66"]Billable[/TD]
[TD="class: xl66"]16/17[/TD]
[TD="class: xl66"]Mar[/TD]
[/TR]
[TR]
[TD="class: xl66"]CLAN[/TD]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]Approved[/TD]
[TD="class: xl66"]Billable[/TD]
[TD="class: xl66"]16/17[/TD]
[TD="class: xl66"]Mar[/TD]
[/TR]
[TR]
[TD="class: xl67"]CLAN[/TD]
[TD="class: xl67"]Gigi[/TD]
[TD="class: xl67"]20[/TD]
[TD="class: xl67"]Approved[/TD]
[TD="class: xl67"]Billable[/TD]
[TD="class: xl67"]16/17[/TD]
[TD="class: xl67"]Mar[/TD]
[/TR]
[TR]
[TD="class: xl67"]CLAN[/TD]
[TD="class: xl67"]Gigi[/TD]
[TD="class: xl67"]20[/TD]
[TD="class: xl67"]Approved[/TD]
[TD="class: xl67"]Billable[/TD]
[TD="class: xl67"]16/17[/TD]
[TD="class: xl67"]Mar[/TD]
[/TR]
[TR]
[TD="class: xl66"]CLAN[/TD]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl66"]30[/TD]
[TD="class: xl66"]Approved[/TD]
[TD="class: xl66"]Billable[/TD]
[TD="class: xl66"]16/17[/TD]
[TD="class: xl66"]Apr[/TD]
[/TR]
[TR]
[TD="class: xl66"]CLAN[/TD]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl66"]30[/TD]
[TD="class: xl66"]Approved[/TD]
[TD="class: xl66"]Billable[/TD]
[TD="class: xl66"]16/17[/TD]
[TD="class: xl66"]Apr[/TD]
[/TR]
[TR]
[TD="class: xl67"]CLAN[/TD]
[TD="class: xl67"]Gigi[/TD]
[TD="class: xl67"]40[/TD]
[TD="class: xl67"]Approved[/TD]
[TD="class: xl67"]Billable[/TD]
[TD="class: xl67"]16/17[/TD]
[TD="class: xl67"]Apr[/TD]
[/TR]
[TR]
[TD="class: xl67"]CLAN[/TD]
[TD="class: xl67"]Gigi[/TD]
[TD="class: xl67"]40[/TD]
[TD="class: xl67"]Approved[/TD]
[TD="class: xl67"]Billable[/TD]
[TD="class: xl67"]16/17[/TD]
[TD="class: xl67"]Apr[/TD]
[/TR]
</tbody>[/TABLE]


The Pivot table I have has a calculated field inserted called "Utilization" (ie billable hours / 168).

So if a user had 20 billable hours (which is the case for Ben above, in March), their utilization would be 20/168 = 12%.

The table below shows the billable hours for Ben and Gigi in March and April

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]Ben[/TD]
[TD="class: xl66, width: 64"]Gig[/TD]
[/TR]
[TR]
[TD="class: xl66"]Mar[/TD]
[TD="class: xl67"]20[/TD]
[TD="class: xl67"]40[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apr[/TD]
[TD="class: xl67"]60[/TD]
[TD="class: xl67"]80[/TD]
[/TR]
</tbody>[/TABLE]

The table below shows their utilization for those months (billable hours / 168), which are the figures shown in my Pivot table.

[TABLE="width: 275"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Ben [/TD]
[TD]Gigi[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]24%[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD="align: right"]36%[/TD]
[TD="align: right"]48%[/TD]
[/TR]
</tbody>[/TABLE]


However, the Pivot table should show the utilization figures below, which are cumulative, based on the two months.

eg Ben did 20 billable hours in Mar, so his utilization then was 12%.

In April he did 60 billable hours. So his cumulative utilization in April was 20 (from March) + 60 (from April) / (168 x 2) = 24% (or 23.8%).

I'm sure there is a genius out there who knows how to display this in the Pivot Table! Please can you help?

Thanks in advance.

[TABLE="width: 275"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Ben[/TD]
[TD]Gigi[/TD]
[/TR]
[TR]
[TD]Mar [/TD]
[TD]12%[/TD]
[TD]24%[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]24%[/TD]
[TD]36%[/TD]
[/TR]
</tbody>[/TABLE]



****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Client[/TD]
[TD="class: xl65, width: 64"]User[/TD]
[TD="class: xl65, width: 64"]Hours[/TD]
[TD="class: xl65, width: 64"]State[/TD]
[TD="class: xl65, width: 64"]Billable[/TD]
[TD="class: xl65, width: 64"]FY[/TD]
[TD="class: xl65, width: 64"]FYmonth[/TD]
[/TR]
[TR]
[TD="class: xl66"]CLAN[/TD]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]Approved[/TD]
[TD="class: xl66"]Billable[/TD]
[TD="class: xl66"]16/17[/TD]
[TD="class: xl66"]Mar[/TD]
[/TR]
[TR]
[TD="class: xl66"]CLAN[/TD]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]Approved[/TD]
[TD="class: xl66"]Billable[/TD]
[TD="class: xl66"]16/17[/TD]
[TD="class: xl66"]Mar[/TD]
[/TR]
[TR]
[TD="class: xl67"]CLAN[/TD]
[TD="class: xl67"]Gigi[/TD]
[TD="class: xl67"]20[/TD]
[TD="class: xl67"]Approved[/TD]
[TD="class: xl67"]Billable[/TD]
[TD="class: xl67"]16/17[/TD]
[TD="class: xl67"]Mar[/TD]
[/TR]
[TR]
[TD="class: xl67"]CLAN[/TD]
[TD="class: xl67"]Gigi[/TD]
[TD="class: xl67"]20[/TD]
[TD="class: xl67"]Approved[/TD]
[TD="class: xl67"]Billable[/TD]
[TD="class: xl67"]16/17[/TD]
[TD="class: xl67"]Mar[/TD]
[/TR]
[TR]
[TD="class: xl66"]CLAN[/TD]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl66"]30[/TD]
[TD="class: xl66"]Approved[/TD]
[TD="class: xl66"]Billable[/TD]
[TD="class: xl66"]16/17[/TD]
[TD="class: xl66"]Apr[/TD]
[/TR]
[TR]
[TD="class: xl66"]CLAN[/TD]
[TD="class: xl66"]Ben[/TD]
[TD="class: xl66"]30[/TD]
[TD="class: xl66"]Approved[/TD]
[TD="class: xl66"]Billable[/TD]
[TD="class: xl66"]16/17[/TD]
[TD="class: xl66"]Apr[/TD]
[/TR]
[TR]
[TD="class: xl67"]CLAN[/TD]
[TD="class: xl67"]Gigi[/TD]
[TD="class: xl67"]40[/TD]
[TD="class: xl67"]Approved[/TD]
[TD="class: xl67"]Billable[/TD]
[TD="class: xl67"]16/17[/TD]
[TD="class: xl67"]Apr[/TD]
[/TR]
[TR]
[TD="class: xl67"]CLAN[/TD]
[TD="class: xl67"]Gigi[/TD]
[TD="class: xl67"]40[/TD]
[TD="class: xl67"]Approved[/TD]
[TD="class: xl67"]Billable[/TD]
[TD="class: xl67"]16/17[/TD]
[TD="class: xl67"]Apr[/TD]
[/TR]
</tbody>[/TABLE]


</body>

 

[mart37]

with 2 columns

[TABLE="width: 398"]
<tbody>[TR]
[TD]H2[/TD]
[TD]=C2+SUMIFS($C$1:C1,$B$1:B1,B2,$G$1:G1,"<>"&G2)/COUNTIFS($B$1:$B$9,B2,$G$1:$G$9,G2)[/TD]
[/TR]
[TR]
[TD]I2[/TD]
[TD]=1/COUNTIFS($B$2:$B$9,B2,$G$2:$G$9,G2)+SUMIFS(I$1:I1,B$1:B1,B2,G$1:G1,"<>"&G2)/COUNTIFS($B$2:$B$9,B2,$G$2:$G$9,G2)[/TD]
[/TR]
</tbody>[/TABLE]

utillizaition = hours/(168*cum2)

 

[Mr2017]

Hi Mart

Thanks for posting an answer to this.

I tried what you posted, but it didn't quite work, and I had to make some assumptions.

I assume I should name cells H1 and I1 cum1 and cum2, respectively?

Also, when I applied the formulas and re-did the pivot table, it gave me the figures below for the cumulative utilization for April (71% and 95% for Ben and Gigi, respectively. However the figures should be 24% and 36%, respectively ie Ben's billable hours from March (20)+Ben's billable hours from April (60) = 80 / 168*2 = 24%.

With Gigi, the sum of the billable hours from Mar and April are 120. 168 hours are available each month, so we'd do 120 / 168*2 = 36%.

Have I missed something?

[TABLE="width: 351"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Ben[/TD]
[TD]Gigi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]12%[/TD]
[TD]24%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]71%[/TD]
[TD]95%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


NB in the real data, some users have billable hours listed more than once in one day, depending on the task that was being carried out that day eg client meeting - 1 hour, analysis - 2 hours, etc. Let me know if you want me to clarify anything further.

Thanks in advance.

 

[mart37]

sorru
utilization = Cum1 /(168* Cum2)