Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

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

Need Help

AlB

7 years ago

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

AlB

Community Champion

7 years ago

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

mbjerggaard

New Member

6 years ago

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
6 years ago
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