Calculated Field / Item

[guptamols]

Hi, I have the below data and i want to calculate score. As of now, i calculate this after the pivot area. Can i calculate it inside the pivot using calculated field / item ?

[TABLE="width: 528"]
<tbody>[TR]
[TD]Transaction Id[/TD]
[TD]Region[/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD]P3[/TD]
[TD]P4[/TD]
[TD]Good[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]A989[/TD]
[TD]KAR[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD]A789[/TD]
[TD]KAR[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD]A900[/TD]
[TD]KAR[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD]A968[/TD]
[TD]GUJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD]A455[/TD]
[TD]GUJ[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD]A456[/TD]
[TD]KAR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD]A910[/TD]
[TD]GUJ[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD]A915[/TD]
[TD]GUJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD]A457[/TD]
[TD]KAR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD]A458[/TD]
[TD]KAR[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]75%[/TD]
[/TR]
</tbody>[/TABLE]

Pivot looks like below

[TABLE="width: 204"]
<tbody>[TR]
[TD]REGION[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]GUJ[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]KAR[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

Have to calculate % TRUE as a score inside the pivot itself

 

[sgroath]

Inelegant approach but should give you what you are asking for...

Assuming Score is column H, put this in I2 and copy it down the column (you can format this as a %): =--G2

Expand your pivot table source to include the new column (which I called % True) and in your pivot table:

Drag % True to your values section
Select the drop down for this new field and select Value Field Settings
Summarize value field by Average and make number format Percentage
Hide the 2 columns that got added inside the pivot table showing Average % of True for both the TRUE and FALSE column labels (this is the inelegant part), keeping the Total Average of % True column