Is there a formula to get the average spend/ count for a cross tab?

[jschill2628]

Is there a way to get average spend by count. What I mean by this is that I have a cross tab and I want to have my distinct count, total spend, and the average spend by count.

What I am getting is my distinct count, my total spend, average spend by transaction.

The problem with this is that there can be 3 claims (count) but 100 transactions. The formula (cross tab) is showing the average by transaction not by claim count.

Is there a formula to get the average spend/ count for a cross tab?

What I am getting:
Count: 3
Total $: 31,156
Average $: 1,416

What I want
Count: 3
Total $: 31,156
Average $: 10,385

Thank you!

 

[lbass]

Create a formula like this:

sum({table.spend})/distinctcount({table.claims})

Add this instead of your average summary, and insert a maximum or average on it.

If you have a row field in the crosstab, then you would have to add a group condition to the formula:

sum({table.spend},{table.group})/distinctcount({table.claims},{table.group})

-LB