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

Andi Mohr
  • 4,315

4 Answers4

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.

Andi Mohr
  • 4,315
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.

harrymc
  • 498,455
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")
   )
Andi Mohr
  • 4,315
0

this works for me:

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

Jens
  • 1