Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have a problem if someone can help me with:
let us say i have the following table
City State Value A
Houston TX 10
Dallas TX 8
Belmont CA 12
Mountain View CA 6
now i have to use some formula where I compare the value at city level with average at State level, say
if value A for Houston (which is 10) is greater than average of state TX (which 9) then Value B 100.
I tried using aggr function but I think i am not able to use is correctly because if I do not give city as dimension, i only get calculation for one city and if I give city as dimension i obviously get average at the city level and not at state level.
thanks,
Manoj
Or maybe
City | =Only(Aggr(If(Avg(TOTAL <State> [Value A]) <= Avg([Value A]), 100), City, State)) |
---|---|
100 | |
Belmont | 100 |
Houston | 100 |
May be this?
=Aggr(If(Avg(TOTAL <City> [Value A]) <= Avg([Value A]), 100), City, State)
Thanks Sunny but value B has to be at city level so it should be like
City State Value A Value B
Houston TX 10 100
Dallas TX 8 (not hundred since 8 is less than state average 9, say value is 90)
Belmont CA 12 100
Mountain View CA 7 (not hundred since 7 is less than state average 9.5, say value is 90)
(i have changed Values A slightly to avoid any confusion)
Or maybe
City | =Only(Aggr(If(Avg(TOTAL <State> [Value A]) <= Avg([Value A]), 100), City, State)) |
---|---|
100 | |
Belmont | 100 |
Houston | 100 |
Thanks swehi! Works perfectly!
I need to understand this aggr function better because I always have problems using it.
Hi,
Here AGGR is not needed, if you follow the below:
Chart: Pivot, Straight
Dimension: State, City
Expression: If(Avg(TOTAL <State> [Value A]) < [Value A],100)
Thanks Saran, you are right. This works too.
Well the Aggr() would be needed if you need to fix the Total up top. For the current example, Only(Aggr()) or without Aggr() will give same answer of 100. But if the two values were 80 and 100, you would see a -. To get a sum, you can use Sum(Aggr()) or to get Avg, you can use Avg(Aggr())
Hi Saran,
I just checked and it seems that with your solution, I have to have State in the dimension whereas if I use aggr I don't need State in the dimension.
Yes that's true as well