# Business Intelligence - PowerPivot suppress rows in a PivotTable based upon a measure

• ### Question

• Crystal reports have the capability to suppress rows in a report
based upon a calculation.  I have a pivot table that has two scenarios
that I would like to compare.  There are 4 measures:

1. Total Dollars := sum(Dollars)

2. S1 Total Dollars := calculate (Total Dollars,scenario=1)

3. S2 Total Dollars := calculate (Total Dollars,scenario=2)

4. Delta  s1 minus s2 := S1 Total Dollars - S2 Total Dollars

When Delta  s1 minus s2 = 0 suppress the row.

Sunday, August 11, 2013 3:28 PM

### Answers

• There is native Excel pivot table functionality for this, and it's been there for several versions.

From the pivot table, select the rows axis filter (if you have multiple fields on the rows axis, make sure this is set to filter the desired field).

Then under "Value Filters" select "Does Not Equal" and set to zero.  This will suppress the values on the rows axis that equal zero.

Let me know if that helps.

Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

• Proposed as answer by Sunday, August 11, 2013 5:00 PM
• Marked as answer by Monday, August 19, 2013 1:11 PM
Sunday, August 11, 2013 3:54 PM

### All replies

• There is native Excel pivot table functionality for this, and it's been there for several versions.

From the pivot table, select the rows axis filter (if you have multiple fields on the rows axis, make sure this is set to filter the desired field).

Then under "Value Filters" select "Does Not Equal" and set to zero.  This will suppress the values on the rows axis that equal zero.

Let me know if that helps.

Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

• Proposed as answer by Sunday, August 11, 2013 5:00 PM
• Marked as answer by Monday, August 19, 2013 1:11 PM
Sunday, August 11, 2013 3:54 PM
• Thanks, It worked.  I made a minor change.  Sometimes, it the line of Power Pivot  and excel blurs for me.  Thanks again.

Peter

Monday, August 19, 2013 7:48 PM