• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Show items with no data problem

W

westcm

New Member
Hi all, just wondering if this is a problem for others


I would like to show items with no data on subcategories of data in a pivot table (excel 2010) .

When I do this, the pivot table shows every item for every sub category and category.

E.g for three categories which each have 6 separate and different subcategories, pivot table repeats

Each subcategory three times whereas I only want to display subcategories directly relating to each category.


I've dug around but I can't find any setting or anything so I am assuming its not possible but

I thought I shd ask in case there is some experience that can be shared


I hope this makes sense


:) chris
 
Chris


Firstly, Welcome to the Chandoo.org Forums


Isn't this just a matter of filtering the appropriate fields to select the Blank data?

You could possibly add a Slicer to the PT and use that also


Can you post your file to allow us to assist further

refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hui, thanks for ur quick response and pls accept my apologies for not reciprocating :)


In the file, I have a pivot that is filtered by location. The pivot has three columns: outcome, program and $


Each program is associated with only one outcome. I wanted to show all programs under each outcome for each location

Regardless of whether a program exists at that location or not.


I hope this makes sense :s.


I really appreciate any advice that u can give me - thanks!

(I hope this Dropbox thingy works :))

http://db.tt/sxPPWe2f
 
Chris


If the Field List dialog isn't showing

Right Click on the Pivot table and select Show Field List


On the Field List, Drag the Outcome field to the Report Filter box
 
GUI, thanks so much for ur response. This is a

Good compromise solution. However, I kinda need

The outcome field as a row label. It seems that

Excel cant see the dependant relationship between

the program and outcome fields? I think I might try using

Countifs.


Thanks again for all ur help


:) chris
 
Chris


Ok, Try the following

Add the $ field as a Sum $ field

Right click on any Row Label, Filter, Value Filters

Sum of $ is Greater than 0

Apply
 
Hi hui, sorry for the autocorrect on ur name in the above :)


Anyway, thanks for trying to find a solution for this - much appreciated.


Unfortunately, when I tried ur suggestion, the report still filters out items

With no values. :|


thanks again :)
 
Hi Chris


Don't give up, we will get there

I have uploaded the file to my new Google Drive

Lets see how this goes?

Try the file at this location: https://docs.google.com/open?id=0B1CHigZ0QSgHeUpFUEx4dll4cDg


if that doesn't work use this: https://www.dropbox.com/s/wmfr6ycw403h2hq/Chandoo%20pivot%20question_Hui.xlsx
 
Hi Hui, Ive been diverted to other jobs so just getting back to this. thanks again for ur ongoing support. I accessed the google version - not sure whats changed? :) Chris
 
Hui, thanks again for your patience and efforts; i hope im not being too difficult LOL Hi the objective was to show the fields with no records as well as those with entries when any location is selected from the location filter. But i dont want programs listed against outcomes to which they dont belong :S


:) Chris
 
You must log in or register to reply here.
Back
Top