Calculated Field Returning Incorrect Results Sometimes Percentage Exceeds 100%
Published: 28 Mar 2013 Last Modified Date: 02 Nov 2020
A calculated field is returning incorrect results. For example, a field with the formula:
is not returning the expected ratio, percentage total exceeds 100%, etc.
Aggregate the measures within the calculated field. For example, change the calculated field's formula: [Profit]/[Sales] to SUM[Profit]/SUM[Sales]
Remove the calculated field from the view and then re-add it to the view.
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
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.