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:

Client	User	Hours	State	Billable	FY	FYmonth
CLAN	Ben	10	Approved	Billable	16/17	Mar
CLAN	Ben	10	Approved	Billable	16/17	Mar
CLAN	Gigi	20	Approved	Billable	16/17	Mar
CLAN	Gigi	20	Approved	Billable	16/17	Mar
CLAN	Ben	30	Approved	Billable	16/17	Apr
CLAN	Ben	30	Approved	Billable	16/17	Apr
CLAN	Gigi	40	Approved	Billable	16/17	Apr
CLAN	Gigi	40	Approved	Billable	16/17	Apr

<colgroup><col width="64" span="7" style="width: 48pt;"></colgroup><tbody>
</tbody>

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

	Ben	Gig
Mar	20	40
Apr	60	80

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

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

	Ben	Gigi
Mar	12%	24%
Apr	36%	48%

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

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.

	Ben	Gigi
Mar	12%	24%
Apr	24%	36%

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Client	User	Hours	State	Billable	FY	FYmonth
CLAN	Ben	10	Approved	Billable	16/17	Mar
CLAN	Ben	10	Approved	Billable	16/17	Mar
CLAN	Gigi	20	Approved	Billable	16/17	Mar
CLAN	Gigi	20	Approved	Billable	16/17	Mar
CLAN	Ben	30	Approved	Billable	16/17	Apr
CLAN	Ben	30	Approved	Billable	16/17	Apr
CLAN	Gigi	40	Approved	Billable	16/17	Apr
CLAN	Gigi	40	Approved	Billable	16/17	Apr

<colgroup><col width="64" span="7" style="width: 48pt;"></colgroup><tbody>
</tbody>

</body>

 

[mart37]

with 2 columns

H2	=C2+SUMIFS($C$1:C1,$B$1:B1,B2,$G$1:G1,"<>"&G2)/COUNTIFS($B$1:$B$9,B2,$G$1:$G$9,G2)
I2	=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)

<tbody>
</tbody>

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?

Row Labels	Ben	Gigi
16/17
Mar	12%	24%
Apr	71%	95%

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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)