Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to write expression Avg(sum(x)) while creating a measure but am unable to do so.
Here is an example
Country, State, Population
US, NY, 10
US, NJ, 16
IND, DE, 20
IND, MU, 18
Now lets say your goal is to get the Sum by each country and then display the average in a text box
Avg(Aggr(Sum(Population), Country, State))
the above will give you (10+16) + (20 + 18) divided by 2 => 32
You will need to add Aggr() in between them
Avg(Aggr(Sum(x), ListOfDimensions))
Can you please elaborate listofdimensions by a working example
Here is an example
Country, State, Population
US, NY, 10
US, NJ, 16
IND, DE, 20
IND, MU, 18
Now lets say your goal is to get the Sum by each country and then display the average in a text box
Avg(Aggr(Sum(Population), Country, State))
the above will give you (10+16) + (20 + 18) divided by 2 => 32
Try just Avg(x)
Hi Sunny,
Maybe Avg(Aggr(Sum(Population), Country, State))
= 32 ?
Hi Siddharth,
Sunny's dead right (as usual) suggesting use of the Aggr function.
Consider this straight table:
Country | Sum(Population) |
---|---|
64 | |
IND | 38 |
US | 26 |
If we change the total mode to Avg in the expressions tab we get this
Country | Sum(Population) |
---|---|
32 | |
IND | 38 |
US | 26 |
The straight table gives you the answer you want, 32.
The expression
Aggr(sum(Population), Country)
can be thought of somehow creating an in-memory straight table with dimension Country and expression sum(Population).
If we wrap this in Avg() :
Avg(Aggr(sum(Population), Country))
that's like setting the Total Mode of a straight table to Avg and this will return the answer we're looking for in a textbox.
Aggr is incredibly useful!
cheers
Andrew
Hi Sidharth,
If you want it to group by country wise then use below exp:
Maybe Avg(Aggr(Sum(Population), Country))
If you want it to normal Avg then use below exp:
Avg(population)
Thanks,
Arvind Patil
Yes you are right