Show Items With No Data on Rows - Slicers - Functionality Question

[CSMcVey]

I have a PowerPivot workbook with a few connections to SQL server. I am working on a Pivot Table.

My row label value is Agent. My column value is week. I am showing the sum of products in the details.

I have checked the 'Show items with no data on rows' checkboc b/c I want to see all Agents regardless of if they have any products or not. This works fine

However, I have a number of slicers. When I use a slicer, it does not update the data. I assume b/c I have told it to show me the rows with no data.

I would like the slicers to continue to filter the data, for example if a sales rep is selected from the sales rep slicer I would want to see only those agents for that sales rep - both rows with data and rows without.

Am I missing something? If not, is there a work-around for this?

 

[powerpivotpro]

Hrm, I just tried this out and it seems to be working for me. Send me an email and I will send you the workbook. My address is in my sig.

 

[ckoester]

"Show Items With No Data" Does Not Honor Report Filter

I'm having the same problem. I'm trying to filter a report to show all divisions for a particular department, including those without data. When I select "Show items without data", the pivot table displays all divisions for all departments.

If I change the department filter, the data does change, but I would expect to see only divisions that belong to the filtered department.

 

[mdrew9]

Re: "Show Items With No Data" Does Not Honor Report Filter

ckoester, so in the last part, what are you seeing if you filter your dept you are seeing Divisions that aren't within that dept??

 

[ckoester]

Re: "Show Items With No Data" Does Not Honor Report Filter

mdrew9 - that is correct.

Filter for dept, "Show items with no data" unchecked for division - I see only divisions with data for the filtered department.

Filter for dept, "Show items with no data" checked for division - I see all divisions regardless of the dept filter, but the numeric data for the filtered dept remains correct.

 

[mdrew9]

Re: "Show Items With No Data" Does Not Honor Report Filter

mdrew9 - that is correct.

Filter for dept, "Show items with no data" unchecked for division - I see only divisions with data for the filtered department.

Filter for dept, "Show items with no data" checked for division - I see all divisions regardless of the dept filter, but the numeric data for the filtered dept remains correct.

Sorry I am slow, so the struture of the pivot table remains the same for the two, but when you filter the data values are limited?

 

[jls33]

Re: "Show Items With No Data" Does Not Honor Report Filter

I have the same issue with "Show Items With No Data" and how I am expecting it to work.

My scenario: I have a pivot table showing a list of products (row label) and their order amount totals (value). I don't have any column labels.

If I check "Show Items With No Data" for the product field then I expect to see all products even if they don't have any order amounts tied to them. This works fine.

If I apply a filter, like the supplier of the products, and choose just one supplier, I would expect that only the products tied to that supplier would show up (regardless if they have order amounts tied to them or not). Instead, the filter only filters out products that have order amounts tied to them. It does not filter out products without order amounts.

The result set is a list of products that get filtered correctly if they have order amounts tied to them ...plus a list of ALL products without order amounts tied to them regarldess of which supplier they belong to.
In other words, the filters only work for products that have value data tied to them. The filters do not work for products without order data tied to them (even though they are tied to specific suppliers).

Is that by design? Is there any way to show just products tied to those suppliers and without order data?

 

[ckoester]

Re: "Show Items With No Data" Does Not Honor Report Filter

jls33 - I agree that the "Show items with no data" doesn't work as expected when using filters. Unfortunately, the best way that I've found to deal with this is to create dummy records. In this case you'd have to create dummy records for all products and all suppliers. Try it with just one product first and refresh the pivot table to make sure it's being captured. I think that the dummy records must have a value for any value that is displayed in the pivot table.

 

[AlbertoFerrari]

Re: "Show Items With No Data" Does Not Honor Report Filter

Unfortunately, the reason why you don't see a division under a department, is not because there is a relationship between departments and divisions, as you might expect thinking at your model, but only because, in your fact table, there is no data for that department/division pair. By default, Excel hides rows with blank values and, doing this, mimics the existence of a relationship between divisions and departments. But, as soon as you disable this feature, it shows exactly what it does, i.e. check for all pairs and show all of them, even blank ones. Technically speaking, it performs a crossjoin of the two columns and shows all of it.
You basically have two options here:
You put both the department and the division in a single table. Doing so, you define the relationship between the two entities in the table and, when you use columns of that table to perform the filtering, you can enable the "show items with no data" and it will work as you expect. Clearly, this requires a change in the data model, which might not be convenient in your specific scenario.
Another option is to fool Excel and force it to show data even when such data does not exist. You can do it adding dummy rows, but this is... well, not so elegant. Another option is to play with DAX and 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.
I have created a simple worksheet with Depts, Divisions and a fact table called Numbers, with a single column "D" containing a number to sum.
Writing the DAX code in this way:

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

The PivotTable shows zero for the divisions of the department selected, even if there is no data for them, while it hides divisions which do not belong to the department (because of the IF COUNTROWS). Clearly, because you might not like zero, you can modify the behavior of Excel to hide zeros using the options, and the result will be what you wanted at the beginning.
You can take a look at the demo workbook here: https://skydrive.live.com/redir?resid=42D20348F36560F1!104&authkey=!AHeLoVgfzhY7kY0

Alberto
SQLBI