Excel 2010 PowerPivot: How to show items with no data

daxmicrosoft excelmicrosoft-excel-2010pivot tablepowerpivot

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

Best Answer

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.

Related Question