# Calculated Field in Pivot Grid

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?

I don’t know if I understand right. I am trying to reproduce it but I think it works fine. Can you send me more information as sample data?

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.

That seems to work for that scenario, but I’m trying to perform a calculation and it does not work. Try an example similar to mine, please…

(Price_Customer_Extended / Profit_Extended)

or

( (Price_Customer_Extended - Cost_Extended) / Price_Customer_Extended )

I have calculation ([TOTAL_PRICE])/([AMOUNT_BILLED]) in column “Sum of Percent”

Raw data:

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 finally reproduced it and I investigate now how to solved it.

Thanks
Petr

1 Like

Petr entered the issue as QAT-13586. We are wrapping up the 5.0 release so we won’t be able to fit that in so we will look at for the next release

Thank you! Keep up the good work.

We were able to add this enhancement. it will be in the 5.0.3 release due out in Mid April.

This appears to be still be an issue in 5.0.4.45

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]

Ahh... I removed the calculated column from the pivot, changed it to Default. Then it allowed me to put it back onto the grid as it was originally.