cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CameronTCD Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User
Super User

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

 

4 REPLIES 4
Super User
Super User

Re: Average of multiple measures

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

 

CameronTCD Frequent Visitor
Frequent Visitor

Re: Average of multiple measures

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!

Super User
Super User

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

 

CameronTCD Frequent Visitor
Frequent Visitor

Re: Average of multiple measures

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
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 310 members 3,479 guests
Please welcome our newest community members: