Average count expression

[apthansh]

Location           Capacity

GA                      20

CA                      10

Month                  ID   Location

1/1/2016              1     GA

1/2/2016              1     GA

1/3/2016              1     GA

1/3/2016              2      CA

1/4/2016              2      CA

1/5/2016              2      CA

Utilization =  (count(distinct([ID])) /sum(Capacity)

How to calculate the average instead of total in the above calc...

[Anil_Babu_Samineni]

Try like this

Count(distinct ID)/sum(Capacity)

[vardhancse]

Avg(count(distinct([ID])) /sum(Capacity)

[Anonymous]

use following :-

Avg(count(distinct([ID]) /sum(Capacity))

Thanks,

[jonathandienst]

You can't nest aggregation functions like that. You probably need something like

Avg(Aggr(Count(Distinct ID]) / Sum(Capacity), Location))