Pivot Table Conditional Sum or Count

Question

I have a data tab with following columns:

State => string  
City   => string  
Person_ID => int  
Status  => values(0/1)  
OnLeave  => values(0/1)

Need to create a Pivot table like below:

Report Filter: State
Row Labels: City
Values: count(Person_ID), sum(Status), sum(OnLeave)

I want to do a conditional count for count(Person_ID) where this should count Person_ID only when OnLeave is 0.

Tried using Calculated Field, it doesn't work since its applied at the pivot table level instead of Data Row. Is there a way to do it directly in Pivot table?

teylyn teylyn · Accepted Answer · 2014-05-05T20:45:12

Add a column to the source data with an IF statement that evaluates the OnLeave column and returns a 1 when true. Then sum that column in the pivot table.

Pivot Table Conditional Sum or Count

2 Answers