trouble with calculated fields in excel for power pivot

[stephme55]

Hello. I'm using power pivot and I have two fields. Customers and claims. I did a distinct count for both of them. Then there is a field that is the sum of an expense for all the items. I wanted to calculate this total divided by distinct customer count to get claims cost per customer. This doesn't work and returns an error. However, if I create a calculated field WITHIN the manage data window for each this items (unique count column and then sum values for claims column) and then create a calculated field based on these calculated field, then it works. Why is this?

[JosephP]

if you don't create a separate measure for each calculation the division calculation is actually the sum of one field divided by the sum of the other

[stephme55]

Hi Joseph P. I didn't exactly follow that What is the seperate measure? Isn't the distinct customer count/claims cost per customer a seperate measure?

[JosephP]

no-if you don't create them as actual measures you can't refer to them in the third calculation. you can only refer to the base fields and then you can't use a distinct count calculation

[stephme55]

Ok, I think I understand. Thanks.