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

Average of multiple measures

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?

1 ACCEPTED SOLUTION

Accepted Solutions 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

Re: Average of multiple measures

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

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

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

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

Announcements Top Kudoed Authors
Users Online
Currently online: 310 members 3,479 guests
Recent signins:
• R3qu1r3m3nt5 • Gricenko_SJ • rayloubayi • manuellchavez • ekeles • HxH • coachVE • jorgemur • kitterific • Grzesiek89 • GVDD • luciaf • dazbutler1 • asifs0862 