Calculated member for average based on Columns count

[rodney_rivera22]

Hi,

I'm trying to get the formula to be able to calculate the average value based on a total amount divided by the count of columns (Months) inside a Cross Tab report. 

Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Total	Average Sales (by month count)
100	400	300	1000	600	300	2700	450 = ( 2700 / 6)

How I can get the number of columns in a dynamic way?  by the way, I'm using SAP Crystal Reports 2013.

Can someone help me with this formula? I'll appreciate any help

Thanks,

[abhilash_kumar · Accepted solution]

Hi Rodney,

Assuming you already have the Calculated Member column setup, you'd use the following code as its Calculation Formula:

GetTotalValueFor("Month Field", 0) / (GetNumColumns-2)

Notice how I've surrounded the 'Month Field' with double quotes and not the usual curly braces for database field.

-Abhilash

[rodney_rivera22 · Accepted solution]

Thanks Abhilash...

Now I want to move the "Average" column to be after the "Total" column.  How can I do that?

Thanks again