Show Details - don't include rows with blank values

[woodeye9]

I'm using a pivot table to count instances of various criteria, pretty straightforward; say 10 rows representing Region, and 3 columns representing Phase in the source table.

Pivot table shows 1 row for Region; 5 rows marked with an X in Phase 1, 3 in Phase 2, and 2 in Phase 3. When I double click on the 5 in Phase 1, I want to only see the 5 records flagged in the source table in Phase 1. Instead, I see all 10 rows.

For the 5 non-Phase 1 rows, the Phase 1 column is blank; the pivot table is counting the 5 rows with the X correctly, but the Show Details feature still calls all 10 rows. Is there a way to make this stop? I haven't been able to find one...

[dflak]

It's difficult to visualize what you are trying to do from a verbal description. Attaching a sample spreadsheet would help a lot.

[woodeye9]

Sure, dflak, thanks for the response. Here's a basic example. Double-click on any of the numbers in the pivot table, and you'll get a new worksheet with all 10 of the regional records, regardless of how many apply to what category you've selected.


Pivot Table Test.xlsx

[dflak]

The issue is with how the source data is organized. It is organized as "flat" records. So when you double click on a row (Region), you will get all records associate with that row. For example the pivot table is taking everything associated with Region B and collecting it on row 6 of the spreadsheet.

To get the results you want, you need to "normalize" the data as shown in the New Data Tab. Then you can run a pivot table as shown in the New Pivot Table tab and get the results you want.

The attached workbook contains code to translate your data table to the new data format. You might find it handy to convert your data if you so wish.

[woodeye9]

Yeah, I was afraid that was the case. I knew that I could make it work that way, but my hope is to have only one row per Program in the detail.It looks like I'm going to have to either adjust the users expectations for the detailed records, or educate on how to filter the detail appropriately.

Thanks for your input!

[dflak]

It depends on what you want to do with the flattened table. If that's the way your customer gives you the data an wants to see the pivot table, then you should be able to adapt the code I provided to convert their input into the normalized structure.

On the other hand, if the flattened table is the end result; that is, something to show the customer, then you can use another pivot table to show the normalized data in flattened format. See the New Pivot Table (2) sheet in the attached.

You have Excel 2013, so you can fill in the blanks in the rows in the report format.