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?

score 5 · Answer 1 · answered May 05 '14 at 20:45

5

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.

answered May 05 '14 at 20:45

teylyn

34,374
4
53
73

I dont want to add a new column in source data, trying to see if its possible in pivot table. Thanks! – Vasanth May 05 '14 at 20:48
In that case you may need to get into PowerPivot and see what can be done there. With regular pivot tables, additional columns in the data source are often the only way to generate a desired outcome in the pivot table. – teylyn Oct 08 '15 at 23:39

score 0 · Answer 2 · answered Aug 05 '14 at 05:26

0

add to report filter: onLeave column, select 0 as filter

answered Aug 05 '14 at 05:26

Deodatus AL Wutun

1

Pivot Table Conditional Sum or Count

2 Answers2