locked
Force a measure to be visible for all rows in pivot table, even when there is no data? RRS feed

  • Question

  • Can I force the following measure to be visible for all rows in a pivot table?

    Sales Special Visibility:=IF(
        HASONEVALUE(dimSalesCompanies[SalesCompany])
        ;IF(
            VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
            ;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
            ;[Sales]
        )
        ;BLANK()
    )

    FYI, I also have other measures as well in the pivot table that I don't want to affect.


    • Edited by JP3O Tuesday, October 13, 2015 12:25 PM
    Tuesday, October 13, 2015 12:25 PM

Answers

  • Add zero to the whole thing. Just +0 outside the outer IF().

    This will force the measure to evaluate to 0 rather than BLANK. Only BLANK is suppressed in the pivot.


    GNet Group BI Consultant

    • Proposed as answer by Michael Amadi Tuesday, October 13, 2015 8:14 PM
    • Marked as answer by JP3O Thursday, October 22, 2015 9:37 AM
    Tuesday, October 13, 2015 2:39 PM

All replies

  • Add zero to the whole thing. Just +0 outside the outer IF().

    This will force the measure to evaluate to 0 rather than BLANK. Only BLANK is suppressed in the pivot.


    GNet Group BI Consultant

    • Proposed as answer by Michael Amadi Tuesday, October 13, 2015 8:14 PM
    • Marked as answer by JP3O Thursday, October 22, 2015 9:37 AM
    Tuesday, October 13, 2015 2:39 PM
  • Or if you are in Excel you can go into the PivotTable properties and turn off the suppression of blank rows.

    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Michael Amadi Tuesday, October 13, 2015 8:14 PM
    Tuesday, October 13, 2015 8:06 PM
  • Add zero to the whole thing. Just +0 outside the outer IF().

    This will force the measure to evaluate to 0 rather than BLANK. Only BLANK is suppressed in the pivot.


    GNet Group BI Consultant

    This is what I needed as I needed to disable suppression of blank rows for this measure, but not all measures in the pivot table
    Thursday, October 22, 2015 9:38 AM