Pivot Table Calculated Field / Item

[tc69]

I have a Pivot Table structured as follows using the first Auto Format option.
I need to customize the items that get displayed in the Units Row Field
column and the Price Data Field Column. Specifically I need the "Units" Row
Field to actually be blank vs. "(blank)" when it's blank or 0 or ideally
dependent on another undisplayed Field's ("UnitType") value being "YN". I
also need the "Price" Data Field to show "INCLUDED" when its value is 0.

I tried creating a Calulated Field for Units with the formula
"=IF(Units=0,"",Units)", but when I tried to add it as a Row Field, it says
"The field you are moving cannot be placed in that PivotTable area". I tried
defining a Calulated Field for Price with the formula
"=IF(TotalPrice=0,"INCLUDED",TotalPrice )", but it shows "#VALUE!" vs.
"INCLUDED". Thanks!

Cat Sub Item Description Unit Name Units Price

Category A-23456789012345678901234567890
Subcagegory A-567890012345678901234567890
Item 1 1234567890 12345 $123456.12
Item 2 1234567890 12345 $123456.12
Subcagegory B-567890012345678901234567890
Item 1 1234567890 12345 $123456.12
Item 2 1234567890 12345 $123456.12

Category B-23456789012345678901234567890
Subcagegory A-567890012345678901234567890
Item 1 1234567890 12345 $123456.12
Item 2 1234567890 12345 $123456.12
Subcagegory B-567890012345678901234567890
Item 1 1234567890 12345 $123456.12
Item 2 1234567890 12345 $123456.12

 

[tc69]

'Anyone? Anyone?' If I should post to another group, please let me know.
Any help would be appreciated.

FYI, the relevant parts of the Source Table is as follows:

Category Subcategory ItemDescription UnitName UnitType Selected Units
TotalPrice
Category A Subcagegory A Item 1 SQ FT. NUM Y 100 1000.00
Category A Subcagegory A Item 2 SQ.FT. NUM Y 100 1000.00
Category A Subcagegory B Item 1 SQ.FT. NUM Y 100 1000.00
Category A Subcagegory B Item 2 SQ.FT. NUM Y 100 1000.00
Category B Subcagegory A Item 1 SQ.FT. NUM Y 100 1000.00
Category B Subcagegory A Item 2 SQ.FT. NUM Y 100 1000.00
Category B Subcagegory B Item 1 SQ.FT. NUM Y 100 1000.00
Category B Subcagegory B Item 2 SQ.FT. NUM Y 100 1000.00

 

[kbenda]

As for the "(blank)" value in the row area, I don't think you can change
the value itself. But you can use conditional formatting to change the
font to white so that you can't see it. I usually use a macro for this,
but you can also do it manually.

Also, maybe you could create a column in the source data table that
uses a modified version of the formula you tried before ("=IF(Units=0,"
",Units)") -- just assign a value " " (space) rather than the
zero-length string. Then use this new column in the Row area rather than
the original Units column.

As for placing a value of "INCLUDED" in the data area, this can't be
done because the data area is strictly for numbers, not text. Below are
a few ideas for possible ways to get across the same information,
depending on whether the number was calculated from real data or from an
empty data set:

1) make a calculated field like you did, but rather than assigning a
value of "INCLUDED", assign a unique and recognizable numerical value
that will stand out and tell you the same thing;
2) use the "for empty cells, show" option in the Pivot Table Options
dialog box for whenever there is no data behind the calculation;
3) in the pivot table wizard layout, put the Price field in the data
area twice, once to sum and once to count. The count will tell you how
many times the row item appeared in the calculation (a count of zero
means an empty data set);
4) use conditional formatting (just like with the "(blank)" issue) to
make zero values (and/or blank values) stand out in the data area.

I hope this helps.