Row level Calculation and Aggregate level calculation allows you to transform values at the data source level of detail and at the visualisation level of detail respectively.
You can use all arithmetic and conditional expressions including IF-ELSE, CASE (1,2,…n) in row calculations. But there is a scenario where the row calculation will give you an answer without any syntax error but incorrect from business logic.
Scenario:
You have a team of sales consultants and each of them pursued different number of prospects. In the below example, we can easily calculate performance for each consultant by simply multiplying the opportunities worked with their individual success rate.
Let’s use following dataset. It’s simple and small so that we can easily see how the calculations are being done.
The dataset describes prospects approached by ten consultants and their actual sales figure.
If you want to create sales conversion rate for each consultant’s sales, we can use the following row-level calculation:
Formula – [Sales completed] / [Prospects approached]
Challenge :
You can get overall team performance by simply averaging Sales Conversion Rate, but wait….are we missing something here?
Yes, this average of sales conversion rate will give us incorrect average as number of prospects approached by each consultant are different.
Please note – this calculation will NOT give any syntax or runtime error but the answer will be logically incorrect.
Solution:
And hence we need Aggregate level of calculation for finding the correct answer.
We will create following calculation for getting aggregate level of sales conversion rate: (SUM([Sales completed]))/(SUM([Prospects Approached]))
The answer using aggregate function is technically and logically correct, so you need to leverage the power or aggregate calculation where applicable.