Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Legend, the 1st one worked!
Thanks so much!