I have two data sources, say A and B and two fields, say Revenue and Sub. What i want is to create a calculated field that will take summed up values of X and divide it by summed up values of X if they belong to a particular year, say 2022.
I was trying something like this:
sum(
if str([Event Year])='2022' then
[A].[Revenue] else 0
end)/
sum(
if str([Event Year])='2022' then
[B].[Sub] else 0
end).
The primary data source can be anything, in this case it is B. The error the above calculation shows is that we need to sum up values taken from a different data source, but we are already using sum() function in the very beginning. Not using sum in the beginning an d using it inside the if function returns the error that aggregated and non aggregated fields cannot be mixed. If i use an attr() function with the year, it does not work either.
Is there any way to go about this problem?
