calculated field in Pivot table - error

[JamesBowdidge]

Hi,

calculated field driving me mad...

every result returns a div/0! error..

data has "value of an appointment" on each row. it also has the "email domain".. Im trying to get an average appointment fee for each domain.. If I add the respective fields to the pivot (IE "count of email domain", and "sum of appointment fee" I can see the 2 numbers.. I simply want the calculated formula to do 1 divded by the other to get the average for each domain...

I suspect its the difference between count and sum, as the count of "appointment fee" would be the same value as count of "domain" but wouldnt that calculate to 0 rather than div/0!?

any advice?

thanks

J

 

[RoryA]

Unless you are using the data model, a calculated field always uses the SUM of its source fields. Since email domain is text, the SUM will be 0 and you'll end up with a DIV/0 error. Is there a reason you can't simply change the Appointment fee calculation to Average rather than sum?

 

[JamesBowdidge]

Unless you are using the data model, a calculated field always uses the SUM of its source fields. Since email domain is text, the SUM will be 0 and you'll end up with a DIV/0 error. Is there a reason you can't simply change the Appointment fee calculation to Average rather than sum?

No there isnt, really.. I was looking for 2 number in reality the true average (as you say just add average to the appointment fee in the pivot table) but in addition to this some of the appointment fees are Free, and I also want the average of only the chargeable ones..

thanks for your help!!

j

 

[RoryA]

Do the free ones show as zero or blank/text in the underlying data?

 

[JamesBowdidge]

Do the free ones show as zero or blank/text in the underlying data?

They show up as £0.00...

 

[RoryA]

Do you have a version of Excel that supports Power Pivot, or can you add a new column to the source data that returns 1 for anything that isn't free?

 

[JamesBowdidge]

Do you have a version of Excel that supports Power Pivot, or can you add a new column to the source data that returns 1 for anything that isn't free?

Dont Know what power pivot is (ill google it later) but I can add the 1 for anything that isnt free.. ill do that now...

 

[RoryA]

If you can do that, it's a simple solution as you can then use that in your calculated field as a counter for your average calculation.