If you could post a sample dataset possible in excel it would be helpful.
Difficult to do this without changing the source data or being really careful...
- In your source data, on a row level add the number of rowitems per claim, e.g. claim 11 has 5 rows, so add a column with nr_of_claimrows = 5. Then you can divide the total paid amount by the number of rows and then add them in your calculation
- Using AVG, MAX or MIN by showing the different claims as a dimension--> this is rather prone to error. See my attached screenprint
I hope this helps...
2012-11-01_1036.png 17.3 KB
Thanks Derk. I will give your solutions a try.
You could also solve this through table calculations - see attached.
You have to be careful about partitioning. For example, the single value has Claims on the left, rev on the right, so that only the first row for each claim has an amount associated with it.
The sum has both on the right because we want to sum all single values. But you still have to set the partitioning for single value in the sum partitioning (via the drop down).
dupes.twbx.zip 8.1 KB