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

Question

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

Thanks for your help!

Answer 1

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".

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---