I have a PivotTable that contains info regarding transactions of different products, and how long each product usually takes to process. I have the product types as the rows of the table, and for the columns I have “Day Group,” or the number of days at or under which the transaction was processed. I have the dollar sum of the transactions as the underlying data, and since anything within 5 days was processed within 10 days, and anything in 10 days was processed in 14, etc., I have chosen “Show Values As % Running Total in Day Group,” so that the percentages accumulate over a product’s row. I’m interested in the difference in the amount of transactions processed within the first 60 vs. 14 days. I’d like to find the percent difference between these two thresholds for each product/row (so if 30% have been processed in 14 days and 90% have in 60, I want 60%, not 300% or 33.3%). Then I’d like to multiply that % by the sum of all sales for that product (I’ve pulled these from a different sheet but that can change). The % difference and some of the total sales numbers that they’re multiplied with can be seen in the first picture.
I may not use Calculated Field and Calculated Item correctly. Thank you for bearing with me.
The problem is that if I add a calculated item (so that I can make a formula subtracting ‘60’ from ‘40’) into the Day Group field, it thinks that that numbers in that column are the row’s total accumulated value, so the row’s other numbers are divided by it for their own percentages. You can see in the second image that leads to problems. I believe this is a problem no matter how you order the columns. I understand the reason for this problem is that since the difference column is part of the Day Group Field, the Field Settings forces the Difference Column values to be part of the running %. The column could possibly be made as its own Field so that I think it can have its own Field Settings, but then I don’t think it has access to the data in the Day Group Field.
Currently, I have used normal formulas in cells off to the right, which you can see in the first image. This works with the current filters, but when those or anything else changes, it presents problems. Obviously it’d be ideal and more robust if I could incorporate this difference calculation directly into my PivotTable. If it’s also possible to not apply Field Settings to certain columns or Field Items, that could also work. Other things I’ve looked into are Slicers, PowerPivot, Macros/VBA, and other PivotTables. I wouldn’t immediately want to use these or another option if it wasn’t dynamic and able to change size and filters along with the original PivotTable. That’s the situation I’m already in with the cells off to the right.
I’ve spent a ton of time on this. Thanks in advance for any help!
