7 Replies Latest reply on Aug 30, 2011 2:50 PM by shaam ramamurthy

# displaying average and grand total

I have a simple chart displaying categories and sales: 7 sales in cateogory A, 12 sales in category B, 7 sales in category c. I include the grand total to show 24 sales, but I would also like to show the average of 8 with the grand total. How do I go about doing that? This seems simple but I can't figure it out.

• ###### 1. Re: displaying average and grand total

Which 8?

• ###### 2. Re: displaying average and grand total

Sorry - the average is 8.66 of sales in those 3 categories. I'd like to show the average with the grand total.

• ###### 3. Re: displaying average and grand total

There's no simple way to show an average like you do with the grand total option. You could create a calculated field avg([sales]) that you could drag over, but that would give you another column of the totals.

• ###### 4. Re: displaying average and grand total

here is my method:

1. create a calc field like:

```IF FIRST()==0 THEN

WINDOW_AVG(SUM([Sales]),0,IIF(FIRST()==0,LAST(),0))
END
```

2. duplicate your Category field

3. place "Category (copy)" on the level of detail

4. set the compute using for the calc field pill to use "Category (copy)"

then you will have the attached, avg of sum of sales.

• ###### 5. Re: displaying average and grand total

Hi Joe, Brilliant solution. Your technique works for subtotals across a variable row or column. How do I apply the same concept to show the grand total (avg) not only across cateories, but also across dates (weeks in my example)? In my attached example, I would like to show the average for each measure (total worked hours, point spans etc) across the 8 week period I have displayed.

Shaam

• ###### 6. Re: displaying average and grand total

1. create a calc field like:

```IF FIRST()==0 THEN

WINDOW_AVG(SUM([Hrs#]),0,IIF(FIRST()==0,LAST(),0))
END
```

2. duplicate your "Date" field

3. place "Date (copy)" on the level of detail (setting the trunc level to Week Number)

4. set the compute using for the calc field pill to use "Date (copy)"

then you will have the attached, avg of sum of Hrs#.

1 of 1 people found this helpful
• ###### 7. Re: displaying average and grand total

Joe,

Works like a charm! Thanks very much!!!