Excel PivotTable - Show Percentages 1

[BobHunter]

A pivot table is driving me insane at the moment.

A very simplified example of the data is below

Week CaseId Fix
FW01 111111 1
FW01 111112 1
FW01 111113 1
FW02 111114 0
FW02 111115 1
FW03 111116 1
FW03 111117 1
FW03 111118 1
FW03 111119 0

What the pivot table should look like is

Week Percentage
FW01 100
FW02 50
FW03 75

Seems simple, but I cannot crack it.

Help appreciated.

 

[GlennUK]

Let me guess your rules .... Fix can only be zero or one?
And you want to compare the actual sum of Fix with the maximum possible sum of Fix?

If so, you could have Sum of Fix, and Count of Fix, and put some formulae outside of the PivotTable to calculate the percentage.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[BobHunter]

Thanks Glenn. You've got it in one.

I do have count of fix and sum of fix in a pivot table, and I am currently calculating the % manually using a formula.

I was hoping some guru might know if this is possible in a pivot table with me having to manually redo the formulae for each refresh of the data.

Any more takers or is this impossible ?

Thanks.

 

[combo]

Taking the advantage of 0-1 fix value, put 'Fix' into data area and choose 'Average' as domain function.

combo

 

[BobHunter]

Nice one combo, that workaround has worked a treat - thanks.