Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
CameronTCD
Helper I
Helper I

Average of multiple measures

Currently I use 3 measures to calculate an average percentage using a (x+y+z)/3 method. 

 

M0v9CkR.png

 

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?

 

 

 

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

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_ )

 

View solution in original post

12 REPLIES 12
Proday83
Frequent Visitor

I have to say thanks too!

PowerBI_NuB
New Member

Thanks for your help. This saved me a ton of time. 

AlB
Community Champion
Community Champion

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:

 

6fleNA0.png

Do you have any ideas? 

 

No values in any of the measures or related values are categorised as TRUE/FALSE so I'm lost!

AlB
Community Champion
Community Champion

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!!!

Cre8
Regular Visitor

 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?

AlB
Community Champion
Community Champion

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_ )

 


 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.