locked
Regular Excel Pivot table behavior difference vs SSAS Cube RRS feed

  • Question

  • Hello,

    I have a question regarding the behavior of a regular Pivot Table in Excel vs a Pivot Table build on a cube.

    If I create a dummy list in a spreadsheet like this:

    Category  Value
    A 4
    A 2
    B 3
     
    D 5

     and create a Pivot Table from this information, the Pivot will display (C is displayed even though there are no value):

    6
    3
     
    5

     

    However, if I have the same concept in a SQL table (records with a measure of a NULL value), then build a SSAS tabular cube, the Pivot will display the following:

     

    I know that I can go into the option of the Pivot Table and select "Display rows without any data" (or something like this, my Excel is in a difference language), they will show up.  The problem is that will also show up other records not related to my selection on the Pivot table.

    Is the only option to have some kind of fake measure that always needs to be on the Pivot?

    Thank you.

    Thursday, July 7, 2016 5:15 PM

Answers

  • Normally you don't want items without data to show up in a pivot table (also in your case as you mention), but you clearly want some items to show even when there's no data. The question is how Power Pivot would distinguish between items that shouldn't be shown and those that should. If you don't have something that PP can work with, this will not work. If you do have it, you can create a measure doing the check. Still, you need to give each item a value so that it will show (e.g. Category C would have Value 0 instead of blank).

    You can include the check measure in the pivot table, or include it in the measure for Value:

    Value:= IF([CheckMeasure];[BasicValue]+0)

    (The +0 forces a blank value to be converted to zero.)

    • Proposed as answer by Charlie Liao Sunday, July 10, 2016 6:07 AM
    • Marked as answer by Charlie Liao Sunday, July 24, 2016 2:55 AM
    Saturday, July 9, 2016 5:43 AM
    Answerer