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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
Super User
Super User

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!

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?

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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