Forum Discussion
Average of multiple measures
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_ )
Thank you for your response AlB
I have inputed the suggested code however now, when updating the visuals, I recieve the following error:
Do you have any ideas?
No values in any of the measures or related values are categorised as TRUE/FALSE so I'm lost!
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_ )THANK YOU SO MUCH FOR THIS ONE!!!
I didn't ask the question, but this formula definitely came through for me!!
I was able to take 7 different measures, get an average, and then get an "overall average" of all of them combined!!!
Hey, this works great.
Measure = VAR Count_ = INT ( [Measure1] <> BLANK () ) + INT ( [Measure2] <> BLANK () ) + INT ( [Measure3] <> BLANK () ) RETURN DIVIDE ( [Measure1] + [Measure2] + [Measure3], Count_ )But if i want to add the ALL function to this, ignoring filtering - how would that be done?
- AlB
Hi Something like
CALCULATE([Measure1], ALL (Table1) )applied to each of the measures should work. You would need to provide more details for a more accurate answer though
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Thank you for this solution it is exactly what I was looking for...
Awesome, Thanks a ton for this!
You saved my day! THANKS A LOT!
