Let’s say that I have a two columns in Pivot Grid: Sales and Profit. I want to create a calculated field “Margin.” I click the sigma button and create a calculated field: [Profit] / [Sales].
This works if the pivot grid is showing the part_number detail, but when the part_number if removed and I want to summarize by a department/category, then the calculation is wrong (it is just summing the margin percentages for each part_number that is within the department/category.
How can I set this calculated field to summarize correctly?
Here is an example: the dataset is at the part number level and each part is flagged as either being in the “Buy/Sell” or the “Make” group. The goal of the pivot is to aggregate on the Buy/Sell - Make groups. Summarizing the sales and profit are straight-forward…just drop them on the grid and they sum. Now I want to add a calculated column that would take the profit and divide by the sales to give the margin percentage. This is yielding margin percentages that are insanely high and it appears that Toad is calculating the margin at the part number level and then summing those values.
Instead, I need Toad to use the summed values of sales and profit.
In the attached example, I would expect the Buy/Sell record to be 224752 / 1093604 = 20.55%
I tried to reproduce your example on our test DB. I have this data:
If I make sum column TOTAL_PRICE then it must equal AMOUNT_BILLED.
But in Pivot it is not correct as you see:
Problem is aggregations in column AMOUNT_BILLED. Use other aggregation (average, min, max) and data will be correct.
Two parts sold that are in the “Clamps and Fasteners” department and in the “Buy/Sell” category. The margin for each part is (Profit Extended / Price Customer Extended). Calculated for each part would be:
NTE-04-LACC19 : 12.07 / 19.95 = 60.50%
NTE-04-LACC39 : 92.82 / 161.82 = 57.36%
Overall for the department and category: 104.89 / 181.77 = 57.70%
But when I use the pivot, it performs the calculation at the part level and then sums the percentages together ( 60.50% + 57.36% = 117.86% ). The margin in the pivot should be 57.70%.
Changing the aggregation method to average for the margin column yields 58.93 (close but not correct), min yields 57.36, and max yields 60.50%.
I might have mixed two calculated field enhancements. (I think) The one I was speaking of is this new radio button selection that changes when a field is calculated. Did you look at and try these options?
I did notice/try those. I just tried and both summary and aggregate return "Error" in every row. Then when I tried to switch it back to Default it says, "Error
In column Margin Percent:The specified expression is invalid. Error 'Can't find property 'PRICE_CURRENT_MONTH'' at '[PRICE_CURRENT_MONTH]' compiling criteria '([PRICE_CURRENT_MONTH] - [COST_OPERATING]) / [PRICE_CURRENT_MONTH]'"
Note: my calculation is: ( [PRICE_CURRENT_MONTH] - [COST_OPERATING] ) / [PRICE_CURRENT_MONTH]
