Forum Discussion

Anonymous's avatar
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's avatar
    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's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

Hi Anonymous 

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

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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!

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity 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_ )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

       

       

    • Rober131's avatar
      Rober131
      Regular Visitor
      2 years ago

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

    • mbjerggaard's avatar
      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's avatar
        AlB
        Icon for Community Champion rankCommunity 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