How to Calculate Average of Average

[#1]

Greetings everyone

I have the following Data

MACHINEID MONTH	MONTH	TIME_MINS
11100	Apr-13	3211
11100	May-13	3511
11100	Jun-13	3600
11100	Jul-13	3302
11121	Jun-13	6212
11121	Jul-13	6331
11132	Jul-13	2212
11143	May-13	5212
11143	Jul-13	5521

Some Machines are not operational in certain months

So I want to Average TIME_MINS  based on MACHINEID. This will give AVERAGE per machine. Then AVERAGE_BY_MACHINEID to find the NET_AVERAGE

So if I manually calculate

What is the expression I should use?

Thank you

[#2]

Hi Ram, you shuould use the aggregate function(aggr) for the above scenario.

PFA the example.

BR,

Abhinava

[tresesco]

May be something like this?

PFA

[ashwanin]

I think you required the data same as per the mentioned pivot.

Create Chart

Make expression of avg of Time_Min as per Machine and put another Net average of whole Time_Min.( if you take the average of whole or average of average , both are same things)

it will gave you the same output as you required.

rgds Ashwani

[rwunderlich · Accepted solution]

I think the solutions suggesting aggr() on on the right track. You can't use TOTAL for the Net Avg, because you want an avg of avg.

Create a Pivot Table and check "show partial sums" for MACHINEID and MONTH on the presentation pane. You can use dimensionality() to limit the display to just the subtotal lines. So for each machine, I would use:

=if(Dimensionality()=1

  ,avg(aggr(NODISTINCT avg(TIME_MINS),MACHINEID, MONTH))

  ,''

)

and for the Net Avg, I would use:

=if(Dimensionality()=0

  ,avg(aggr(

  avg(aggr(NODISTINCT avg(TIME_MINS),MACHINEID, MONTH))

  ,MACHINEID))

  ,''

)

See attached example.

[tresesco]

Thanks Rob, I should have noticed that earlier.

I use the below formula for Avg of avg.

Avg(total Aggr(Avg(TIME_MINS),MACHINEIDMONTH))

Hello Ram, PFA, by now you have multiple solutions. However, Rob's solution is more acceptable.

[#7]

Thanks Abhinava,

Thanks . All together 3 different approach we have.

This is one of the approach.

Great.. everyday I am learning..

[#8]

Hi Rob

Thanks. I am learning new method. First time I  have learned how to use Dimensionality()

Method -2

Thanks.

[#9]

Hi Tresesco

Thanks for the reply. This is a third method.

[Anonymous]

Hi Ram,

For Net Avg I would use following expression.

if(Dimensionality()=0,

  avg(aggr( avg(TIME_MINS),MACHINEID)),''

  )