locked
Business Intelligence - PowerPivot suppress rows in a PivotTable based upon a measure RRS feed

  • 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 Paras DoshiEditor Sunday, August 11, 2013 5:00 PM
    • Marked as answer by Elvis Long Monday, August 19, 2013 1:11 PM
    Sunday, August 11, 2013 3:54 PM
    Answerer

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 Paras DoshiEditor Sunday, August 11, 2013 5:00 PM
    • Marked as answer by Elvis Long Monday, August 19, 2013 1:11 PM
    Sunday, August 11, 2013 3:54 PM
    Answerer
  • 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