12

In standard Excel pivot tables, there is an option for fields that allow you to force display of all items even if there are no results for your current selection. Here's the option:

Pivot Table Field Settings Dialog box

However, using the PowerPivot add-in for Excel 2010 this option is greyed out. Is there a workaround so that I can force all results to appear?

Example scenario - number of bananas sold by month. If I don't sell any bananas in August the powerpivot table doesn't show a column for August at all, it just skips from July to September. I need August to appear, with either a blank number of bananas, or zero.

Any ideas? Maybe a DAX expression is required?

EDIT: to answer harrymc's question, I've created this PivotTable by selecting PivotTable from this menu in the PowerPivot window.

PowerPivot PivotTable insert menu

Improve this question
3
  • On a PowerPivot data set you cannot add-in items without data, therefore it's natural that the "Show Items with No Data" is grayed out. Are you using a Flattened PivotTable as described here ?
    – harrymc
    Commented Sep 30, 2013 at 11:45
  • Thanks for the response harrymc, I'm not using a Flattened PivotTable, I've used a standard PivotTable (see image above).
    – Andi Mohr
    Commented Sep 30, 2013 at 12:33
  • On your first point, I'm not sure I've understood you correctly. You say you cannot add items without data, however my dataset does include August sales for apples. This would normally mean enabling "Show Items with No Data" shows the August column. Are you saying that PivotTables pulling from PowerPivot data behaves differently than any other data source?
    – Andi Mohr
    Commented Sep 30, 2013 at 12:36

4 Answers 4

Reset to default
5

Actually this is a better solution. Thanks to Alberto Ferrari for this one.

You need to build a Months table (ie a list of month names, Jan/Feb/Mar etc - a linked table will work just fine), and create a relationship between your new Months table and your fact table.

Then write a measure like this one: 

NeverBlankUnits:=IF( ISBLANK( SUM(FruitSales[Units]) )
                      , 0
                      , SUM(FruitSales[Units]) 
                   )

EDIT: When you add your new months column into your pivot table, you may find the default sort is frustratingly alphabetical; Apr, Aug, Dec, Feb... Here's a great tutorial showing you how to get round this.

Improve this answer
0

A workaround might be possible to use a Data Analysis Expression (DAX) to replace blanks with zeroes.

DAX can force a zero instead of a blank whenever no data exists in the fact table but you want the row to appear in the PivotTable.

For a simple worksheet with Depts, Divisions and a fact table called Numbers, and with a single column "D" containing a number to sum, write the DAX code in this way:

=IF (
    COUNTROWS (Divisions) > 0; 
    IF (ISBLANK (SUM (Numbers[D])), 0, SUM (Numbers[D]))
)

For more information see :
How to Use DAX in Excel's PowerPivot Add-In
Data Analysis Expressions (DAX) in PowerPivot

This approach was originally suggested in the thread Show Items With No Data on Rows.

Improve this answer
3
  • Thanks - just looking at this approach to see if I can make it work. First thing I've noticed is a small typo in the formula - you've got a semi-colon instead of a comma. (As does the original post by AlbertoFerrari on the link.)
    – Andi Mohr
    Commented Oct 3, 2013 at 9:25
  • Sadly this doesn't seem to do the trick. I've adapted the formula as follows: Units_2:=IF (COUNTROWS('FruitSales') > 0,IF(ISBLANK (SUM (FruitSales[Units])), 0, SUM (FruitSales[Units]))). The PivotTable just shows the units sold in July and September.
    – Andi Mohr
    Commented Oct 3, 2013 at 10:05
  • If no other solution can be found, what is left is to Flatten the PivotTable into a local spreadsheet. The article I have linked-to before might help.
    – harrymc
    Commented Oct 3, 2013 at 10:58
0

A brute force method would be to create a measure for each month. As long as one of the month columns has some data, all month columns will appear. Creating all these individual measures is very tedious though - not ideal.

=CALCULATE(
   SUM([Units])
   ,Filter('FruitSales',[Month Name]="August")
   )
Improve this answer
0

this works for me:

=if(sum(calender[Date])>0;if(isblank(sum(Sales_SalesOrderDetail[LineTotal]));0;sum(Sales_SalesOrderDetail[LineTotal])))

Improve this answer
1
  • Where has calendar[Date] come from? That's not in my example scenario, so how have you added this and how is this table linked in PowerPivot?
    – Andi Mohr
    Commented Nov 28, 2014 at 9:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .