Well, it depends. The formula you've entered in the question can be achieved by using a Calculated Field. Just define a new Calculated Field for your pivot table with the formula =ABS(Sales Amount)
and display that instead of Sum of Sales Amount
.
However, if your raw data contains both positive and negative values for a specific sales agent you need to take a different approach. The above Calculated Field operates on the already summed raw data.
If we for example have the following raw data:
Name Value
A 3
A -2
B 4
B -5
Then we create a pivot table and add a Calculated Field AbsValue with the formula =ABS(Value) we get the following result
Sum of Value Sum of AbsValue
A 1 1
B -1 1
To get a summation of all the absolute values from the raw data, you need to add a new column to the raw data and perform the ABS function there:
Name Value AbsValue
A 3 3
A -2 2
B 4 4
B -5 5
Sum of Value Sum of AbsValue
A 1 5
B -1 9
Use can use a Value Filter for this purpose. Assign a value filter on a Row Label column, usually one that contains one distinct value per row, such as ACC_NO in your example.
Here's a simple example that will hide all negative totals (the 3rd row). I picked the Quarter column to hold the filter (more on this below).
To enter a Value Filter, simply go in the filter drop-down on the column you want to apply your filter on:
How to choose which column to apply a total filter on?
Note that I chose to do the filter on QUARTER instead of ITEM because the elements of this column are not grouped. Therefore, in this case, the filter will be applied to each individual row.
If instead I did the same value filter on the Item column (where we can see Item B is grouped), then the filter would apply on the subtotal for each group. For example, since the sum of Q1 and Q2 for Item B is negative (-5), both Item B rows would be filtered out.
In your sample, all your rows are unique because of the leftmost ACC_NO, so you would get the same result by placing the filter in any column.
Final thoughts:
- If you have multiple totals in your Values area (eg. a count and a sum), you can pick which one to filter against in the dialog.
- If you have a Column Label to split your totals, the Value Filter will be applied against the grand total only.
- Applying a Value Filter on a Column Label instead of a Row Label will perform the filter against the vertical totals at the bottom.
Best Answer
I ended up creating a new table (beside the pivot) that is indexed by cell so even if the order of the pivot changes, the top 20 rows will be what is copied over. Then I created the chart from the new table. Thanks for helping!