How can we make a computed field that computes the ratio of two columns in a pivot table?
As in the embedded screenshot, the Achieved Rate column (using the "Average" method to calculate totals) does not reflect the correct value of Opportunity amount column / Budget column. In this case, the row of month 8 contains 2 records of opportunities, but its Achieved Rate column value 0.83 is not 249,600/306,798; instead, 0.83 is the average of the 2 ratios of Opportunity amount/Budget of those 2 opportunities.
How can I do to display the value of 249,600/306,798 ?
Like
Hello Andrew,
Thank you for your reply.
At first, you can try to change the type of calculation from the "Average" to others for the 'Achieved Rate' column (for example set "Amount" to see how the calculations will be changed). Also you can compare the accuracy of the formula you use if you check the report of the needed records in excel. For that you will need to find the needed section (Opportunity), open the needed records/details for the 8th month, and export those records to excel. After that you will be able to re-check the value you received in the pivot table with the value you got in the excel report for the same period.
Also in the article below you can find more information about the formulas that are being used in the system:
Best regards,
Roman
Roman Rak,
Hi Roman,
Thank you for your reply. But it didn't answer my question.
As the screenshot below, you can see there is obviously no appropriate item to calculate what I need, and of course I had tried every item and no result was right.
On the other hand, I found that in the Forecast section, the (pivot) table shown there is correct! I added a new forecast "FORECAST BY OPPORTUNITY" as shown below, and you can see the three Achieved Rates of the last 3 rows are correct respectively under the 3rd quarter of 2021.
So, what do you think?
Roman Rak,
Thanks in advance. The location is as follows:
Hello. I'm facing a similar issue. Did you manage to solve it?