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

  • 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 Sep 30 '13 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 Sep 30 '13 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 Sep 30 '13 at 12:36
up vote 5 down vote accepted

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.

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.

  • 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 Oct 3 '13 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 Oct 3 '13 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 Oct 3 '13 at 10:58

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")
   )

this works for me:

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

  • 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 Nov 28 '14 at 9:28

protected by Mokubai Dec 21 '15 at 18:07

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.