Forum Discussion
Average of multiple measures
Currently I use 3 measures to calculate an average percentage using a (x+y+z)/3 method. The problem I have however is that there will be no values for one of the measures in some instan...
It's the comparisons that yield the booleans . Cast them to int:
Measure = VAR Count_ = INT ( [Measure1] <> BLANK () ) + INT ( [Measure2] <> BLANK () ) + INT ( [Measure3] <> BLANK () ) RETURN DIVIDE ( [Measure1] + [Measure2] + [Measure3], Count_ )or otherwise:
Measure = VAR Count_ = IF ( [Measure1] <> BLANK (), 1, 0 ) + IF ( [Measure2] <> BLANK (), 1, 0 ) + IF ( [Measure3] <> BLANK (), 1, 0 ) RETURN DIVIDE ( [Measure1] + [Measure2] + [Measure3], Count_ )
AlB
Community Champion
Community ChampionIt's the comparisons that yield the booleans . Cast them to int:
Measure =
VAR Count_ =
INT ( [Measure1] <> BLANK () )
+ INT ( [Measure2] <> BLANK () )
+ INT ( [Measure3] <> BLANK () )
RETURN
DIVIDE ( [Measure1] + [Measure2] + [Measure3], Count_ )
or otherwise:
Measure =
VAR Count_ =
IF ( [Measure1] <> BLANK (), 1, 0 )
+ IF ( [Measure2] <> BLANK (), 1, 0 )
+ IF ( [Measure3] <> BLANK (), 1, 0 )
RETURN
DIVIDE ( [Measure1] + [Measure2] + [Measure3], Count_ )
Works a charm.
Thank you for your help.
- Cameron
AlB wrote:It's the comparisons that yield the booleans . Cast them to int:
Measure = VAR Count_ = INT ( [Measure1] <> BLANK () ) + INT ( [Measure2] <> BLANK () ) + INT ( [Measure3] <> BLANK () ) RETURN DIVIDE ( [Measure1] + [Measure2] + [Measure3], Count_ )or otherwise:
Measure = VAR Count_ = IF ( [Measure1] <> BLANK (), 1, 0 ) + IF ( [Measure2] <> BLANK (), 1, 0 ) + IF ( [Measure3] <> BLANK (), 1, 0 ) RETURN DIVIDE ( [Measure1] + [Measure2] + [Measure3], Count_ )