# Showing % of Pivot Table Field that is Not in Group - #N/A Error

10 Reputation points
2024-07-24T14:29:45.59+00:00

Hello everyone,

I am working on a report using a pivot table that summarizes data in a category > group > label hierarchy. The pivot table rows are based on Category, Group, and Label, and the columns are the values. For each row, I need to show two columns: one for the amount, and one for the percentage of sales. I am using a calculated field with the formula "= amount" to show the percentage of sales as a % of Base Field = Category, Base Item = Net Sales. This works well for all categories, but for the groups, I get a #N/A error.

I have searched for solutions to this problem but haven't found any. Does anyone know how I can show the value as a % of a category in a column for each row, even if the value is not in the category?

Here is an example of what I need the table to look like:

Row Labels:           Amount:           % of Sales:

Net Sales                \$100,000             100.00%

Cost of Sales         \$20,000                 20.00%

Cost of Materials   \$12,000           12.00%

Cost of Labor           \$ 8,000           8.00%

The % of sales should always be the amount of the category or group / amount of the category net sales.

In the first image, please find some sample data and a view of what my current pivot table looks like (showing value as % of, Base Field = Category, Base Item = Net Sales). This leads to #N/A error for the percentage of sales for the groups despite working nicely for the other categories.

Screenshot with % of Category.png

In the second image, we can see what happens if I use show value as % of Parent Total. As you can see, the groups are being divided by their own category's total, not the total of the net sales category (Cost of Materials Totals / Cost of Sales Total, instead of Cost of Materials Totals / Net Sales Total).

screenshot with % of parent total.png

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,713 questions

1. 7,875 Reputation points Microsoft Vendor
2024-07-25T05:43:41+00:00

Hi @Rlamp214

Could you please share us with a simple data sample?

On my following sample, right-click on "sum of amount 2" > Value Field Settings > Show values as "% of Grand Total".

If there are one more category, you may show values as "% Parent Total".

For more, please refer to "Show different calculations in PivotTable value fields".