I have the following data:
Instance1 Instance2
/ \ / \
InstanceA InstanceB InstanceA InstanceC
100 100 100 50
And I have a crosstab report to show that information. In the report filter, I am selecting Instance1 and Instance2. The issue is that I don't know how to totalize the value column, I tried "total (distinct [Instance_second_level_value])" function, the result is as follows:
Instance_count value
field_name 3 150
The Instance_count column is defined as "count(distinct [Instance_second_level_ID])" and the result is right because there is just three diferent instances, I don't want to sum more than once the same instance. But the value column is wrong, it should be 250, the function is distinguishing the value, not the ID..
I tried also the function "total ([Instance_second_level_value])" but without success, the sum added two times the same instance:
Instance_count value
field_name 3 350