3 Replies Latest reply on Jan 6, 2012 12:52 AM by Martin Luxhøj

# Average of average.....?

Hi experts,

I am wondering if there is another way of doing an average calculation. My problem is that I have to calculate a sales price which I do the following way:

IIF([SalesQuantity]=0,0,[NetTurnover]/[SalesQuantity])

But in order to get it to show correctly in my graphs I have to average the measure....this seems odd to me.

Is there a better way of doing this?

P.S. the reason why I'm using Net Turnover is that there are some reductions that do not show in the price that is shown on the invoice.

Thank you in advance

BR

Martin

• ###### 1. Re: Average of average.....?

Sample workbook available?

• ###### 2. Re: Average of average.....?

How about:

`IIF(SUM([SalesQuantity])=0,0,SUM([NetTurnover])/SUM([SalesQuantity]))`

• ###### 3. Re: Average of average.....?

Joe that did the trick! Thank you very much.

I did some reading in the manual and found some extra info under the topic Aggregate Calculations:

"About Aggregate Calculations

Suppose you want to analyze the overall gross margin for every product in your data source.

One way to do this is to create a new calculated field called Margin that is equal to the profit

divided by the sales. Then you could place this measure on a shelf and use the predefined

summation aggregation. In this scenario, Margin is defined as follows:

Margin = SUM([Profit]/ [Sales])

This formula calculates the ratio of profit and sales for every row in the data source, and

then sums the numbers. That is, the division is performed before the aggregation. However,

this is almost certainly not what you would have intended because summing ratios is

generally not useful.

Instead, you probably want to know the sum of all profits divided by the sum of all sales.

That formula is shown below.

Margin = SUM( [Profit]) / SUM([Sales])

In this case, the division is performed after each measure is aggregated. An aggregate

calculation allows you to create formulas like this."