# Calculated Field in Pivot Grid **URL:** \ **Category:** Toad Data Point & Toad Intelligence Central\ **Created:** [September 14, 2018, 3:04pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445 "2018-09-14T15:04:06Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![InfoMan](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/infoman/32/322_2.png) [@InfoMan](https://forums.toadworld.com/u/InfoMan)\ **Post date:** [September 14, 2018, 3:04pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/1 "2018-09-14T15:04:06Z")
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? ---
**Author:** ![Petr.Hodovsky](https://avatars.discourse-cdn.com/v4/letter/p/f0a364/32.png) [@Petr.Hodovsky](https://forums.toadworld.com/u/Petr.Hodovsky)\ **Post date:** [September 20, 2018, 11:53am UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/2 "2018-09-20T11:53:46Z")
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? ---
**Author:** ![InfoMan](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/infoman/32/322_2.png) [@InfoMan](https://forums.toadworld.com/u/InfoMan)\ **Post date:** [September 28, 2018, 1:07pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/3 "2018-09-28T13:07:10Z")
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% ![PivotCalculations](https://global.discourse-cdn.com/quest/original/2X/4/4d7ad151036dcb6165c0583aebd87ee253668107.png) ---
**Author:** ![Petr.Hodovsky](https://avatars.discourse-cdn.com/v4/letter/p/f0a364/32.png) [@Petr.Hodovsky](https://forums.toadworld.com/u/Petr.Hodovsky)\ **Post date:** [October 1, 2018, 12:54pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/4 "2018-10-01T12:54:15Z")
I tried to reproduce your example on our test DB. I have this data: ![data](https://global.discourse-cdn.com/quest/original/2X/c/c100384021084365566575a58526b61acc3bed77.png) If I make sum column TOTAL\_PRICE then it must equal AMOUNT\_BILLED. But in Pivot it is not correct as you see: ![pivot1](https://global.discourse-cdn.com/quest/original/2X/4/4408c83ed292bad2c0b9c7dcdb7bb018a0570eaa.png) Problem is aggregations in column AMOUNT\_BILLED. Use other aggregation (average, min, max) and data will be correct. ![pivot2](https://global.discourse-cdn.com/quest/original/2X/8/85bd84db39a67a9b82c64e8d6266afd3845d9f29.png) ---
**Author:** ![InfoMan](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/infoman/32/322_2.png) [@InfoMan](https://forums.toadworld.com/u/InfoMan)\ **Post date:** [October 1, 2018, 2:20pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/5 "2018-10-01T14:20:23Z")
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 ) ---
**Author:** ![Petr.Hodovsky](https://avatars.discourse-cdn.com/v4/letter/p/f0a364/32.png) [@Petr.Hodovsky](https://forums.toadworld.com/u/Petr.Hodovsky)\ **Post date:** [October 2, 2018, 2:33pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/6 "2018-10-02T14:33:37Z")
I have calculation ([TOTAL\_PRICE])/([AMOUNT\_BILLED]) in column “Sum of Percent” ---
**Author:** ![InfoMan](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/infoman/32/322_2.png) [@InfoMan](https://forums.toadworld.com/u/InfoMan)\ **Post date:** [October 2, 2018, 6:21pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/7 "2018-10-02T18:21:05Z")
Raw data: ![Raw%20Data](https://global.discourse-cdn.com/quest/original/2X/e/eaf05cff35ec629cbf6ad1634b448dd25ef9c061.png) 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%. ![Pivot%20Data](https://global.discourse-cdn.com/quest/original/2X/2/2f4363d2f6105d63bcc6e71519ae4efb5ccbfaea.png) 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%. ---
**Author:** ![Petr.Hodovsky](https://avatars.discourse-cdn.com/v4/letter/p/f0a364/32.png) [@Petr.Hodovsky](https://forums.toadworld.com/u/Petr.Hodovsky)\ **Post date:** [October 3, 2018, 12:10pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/8 "2018-10-03T12:10:50Z")
I finally reproduced it and I investigate now how to solved it. Thanks Petr ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [October 5, 2018, 10:21pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/9 "2018-10-05T22:21:42Z")
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 ---
**Author:** ![InfoMan](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/infoman/32/322_2.png) [@InfoMan](https://forums.toadworld.com/u/InfoMan)\ **Post date:** [October 8, 2018, 12:29pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/10 "2018-10-08T12:29:40Z")
Thank you! Keep up the good work. ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [March 5, 2019, 1:15am UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/11 "2019-03-05T01:15:30Z")
We were able to add this enhancement. it will be in the 5.0.3 release due out in Mid April. ---
**Author:** ![InfoMan](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/infoman/32/322_2.png) [@InfoMan](https://forums.toadworld.com/u/InfoMan)\ **Post date:** [June 6, 2019, 5:46pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/12 "2019-06-06T17:46:54Z")
This appears to be still be an issue in 5.0.4.45 ☹ ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [June 6, 2019, 7:14pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/13 "2019-06-06T19:14:54Z")
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? ![image](https://global.discourse-cdn.com/quest/original/2X/6/610c05dfccccfcce32b9d4fb1b2102ed8678729b.png) ---
**Author:** ![InfoMan](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/infoman/32/322_2.png) [@InfoMan](https://forums.toadworld.com/u/InfoMan)\ **Post date:** [June 6, 2019, 7:40pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/14 "2019-06-06T19:40:17Z")
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] ---
**Author:** ![InfoMan](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/infoman/32/322_2.png) [@InfoMan](https://forums.toadworld.com/u/InfoMan)\ **Post date:** [June 6, 2019, 7:41pm UTC](https://forums.toadworld.com/t/calculated-field-in-pivot-grid/42445/15 "2019-06-06T19:41:45Z")
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.