Avg of an avg

[Blunck-TotalFitouts]

Hi everyone,

I need to work out the average of an average... + not have the result change when selecting a dimension.

The standard average formula of:

Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.Jobs])
/
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.CalMonthYear])

Works when calculating the average number of jobs per cal month for ALL current and past franchises in total. 

However, what I need to know is the average franchise partner jobs per month value.  Figured I could use this:

=Avg({<[Franchise Partners All]=>}Aggr(
Only({<[Franchise Partners All]=>}Count(distinct [Jobs.Jobs])
/
Count(distinct [Jobs.CalMonthYear])), [Franchise Partners All]))

Note - [Franchise Partners All] just has values for the current and past franchise partners.

However this formula produces a "-". Result.

Updating to this:

Avg(Aggr(
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.Jobs])
/
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.CalMonthYear])
,[Franchise Partners All]))

Produces a result.  But it doesn't work for because when a franchise partner is chosen through a filter the result get's updated to match the result for that franchise partner, which is what I don't want.

Please help!

Thanks

Carl

[Kashyap_R · Accepted solution]

Hi

try this

Avg(All Aggr(
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.Jobs])
/
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.CalMonthYear])
,[Franchise Partners All]))

OR

Avg(Aggr(All
(Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.Jobs])
/
Count({1<[Franchise Status]={'Current','Past'},[Franchise Partner]>}distinct [Jobs.CalMonthYear]))
,[Franchise Partners All]))

Hope this helps

Thanks

[Blunck-TotalFitouts]

Legend, the 1st one worked!

Thanks so much!