Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 instances but the total will still be divided by three.
Is there a formula that I can use to calculate the average of the three measures whilst ignoring any measures that contain no values?
Solved! Go to Solution.
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_ )
I have to say thanks too!
Thanks for your help. This saved me a ton of time.
Hi @CameronTCD
Something like
Measure = VAR Count_ = [Measure1] <> BLANK () + [Measure2] <> BLANK () + [Measure3] <> BLANK () // Check how many are non-blank RETURN DIVIDE ( [Measure1] + [Measure2] + [Measure3], Count_ ) // And divide by that 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!
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!!!
You saved my day! THANKS A LOT!
Awesome, Thanks a ton for this!
Thank you for this solution it is exactly what I was looking for...
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?
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
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_ )
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
123 | |
76 | |
71 | |
57 | |
49 |
User | Count |
---|---|
163 | |
83 | |
68 | |
68 | |
59 |