Loading

Calculated Field Returning Incorrect Results Sometimes Percentage Exceeds 100%

Дата публикации: Feb 25, 2025
Описание
A calculated field is returning incorrect results. For example, a field with the formula:

[Profit]/[Sales]

is not returning the expected ratio, percentage total exceeds 100%, etc.

Cause

The calculation used the disaggregated (row) values instead of the aggregated (summed) values of the fields.
Below is an explanation of how each option works using the sample data set shown as a reference.

Sample Data Set
Row IDProfitSales
1100300
250600
3710
In non-aggregated formula, such as [Profit] / [Sales], the value of profit divided by the value of sales in each row, then the results are sum up. So the result of the calculated field becomes 100/300 + 50/600 + 7/10 = 0.3333 + 0.0833 + 0.7 = 1.1163.

In aggregated formula, such as SUM ([Profit]) / SUM ([Sales]), all rows of profit and all rows of sales are sum up first, then the result of profit is divided by that of sales. So the result of the calculated field becomes (100 + 50 + 7)  / (300 + 600 + 10) = 157 + 910 = 0.1725.
Решение
  1. Aggregate the measures within the calculated field. For example, change the calculated field's formula:
    [Profit]/[Sales] to  SUM[Profit]/SUM[Sales]
  2. Remove the calculated field from the view and then re-add it to the view.
Дополнительные ресурсы

Aggregate Calculations (video). 


Номер статьи базы знаний

001473611

 
Загрузка
Salesforce Help | Article