Create Average within measures

[Djangom]

Hi All,
I am wondering if its possible to get an average list of created measure.

Basically I have powerpivot flat table that have the following list :

List Measure1 Measure2 Measure3 Measure4 Measure4

aa 3 4 2 0 0
bb 2 1 8 4 2

Would like to have

List Measure1 Measure2 Measure3 Measure4 Measure4 Measure 5

aa 3 4 2 0 0 3
bb 2 1 3 8 2 4


Measure5 = avg( measure1:measure4)



Thanks all

 

[scottsen]

Not directly, when the data is across columns like that. But certainly you can just... (Measure1+Measure2+Measure3+Measure4+Measure5) / 5 ?

 

[Djangom]

Thanks Scott - That will work if there is a way we can create a counter for the divisor to skip the zero -es.
another words = 3+4+2+0+0/5 = 1.8 will not be correct

Any idea if we can count non empty measures and the divide.

i.e (3+4+2+0+0/3 =3)

Thanks

 

[scottsen]

This all becomes WAY easier if you can unpivot your data into a single column of values (and if needed, another column that describes the type, say "Measure1"). Any chance that can happen?

 

[Tianbas]

you should follow Scott's proposal if possible. If you cant and need to keep that column structure you can build the counter with an IF statement

Counter = IF(Measure1>0,1,0)+IF(....

 

[Djangom]

Thank you - Scott & Tianbas.
Actually I want keep the structure - IF(Measure1>0,1,0) works GREAT!!

Thanks again