Forum Discussion

febrina's avatar
febrina
New Member
4 years ago
Solved

Average from Calculated Measures in different Table

Hi, I have 8 Tables in total

and I have created measure for each table

Now, I have 8 measures.

 

Can I calculate average from those 8 measures? If can, please show me the way.

Thank you!

Need Help
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    4 years ago

    febrina , if these are different measure then you have to try like

    ( [Measure 1] + [Measure 2] + [Measure 3] + [Measure 4] + [Measure 5] + [Measure 6] + [Measure 7] + [Measure 8]) /8

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Hi febrina ,

     

    You can create a measure with below code:-

     

    AVERAGE =
VAR _num = [Measure1] + [Measure2] + [Measure3] + [Measure4] + [Measure5] + [Measure6] + [Measure7] + [Measure8]
VAR _den =
    IF ( ISBLANK ( [Measure1] ), 0, 1 ) + IF ( ISBLANK ( [Measure2] ), 0, 1 )
        + IF ( ISBLANK ( [Measure3] ), 0, 1 )
        + IF ( ISBLANK ( [Measure4] ), 0, 1 )
        + IF ( ISBLANK ( [Measure5] ), 0, 1 )
        + IF ( ISBLANK ( [Measure6] ), 0, 1 )
        + IF ( ISBLANK ( [Measure7] ), 0, 1 )
        + IF ( ISBLANK ( [Measure8] ), 0, 1 )
RETURN
    Divide(_num , _den)

     

     

    Thanks,

    Samarth

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    febrina , if these are different measure then you have to try like

    ( [Measure 1] + [Measure 2] + [Measure 3] + [Measure 4] + [Measure 5] + [Measure 6] + [Measure 7] + [Measure 8]) /8

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • febrina's avatar
      febrina
      New Member
      4 years ago

      Hi amitchandak,

       

      Your solution is worked.

      But, for some cases, there is blank in the measurement

      For example.

      Measure 1 = 3

      Measure 2 = 2

      Measure 3 = null / blank

      Measure 4 = 3

       

      So, I want the new measure like below:

      ([Measure 1] + [Measure 2] + [Measure 3] + [Measure 4])/3

      = (3+2+3)/3

      = 2.67

       

      Could you help me on this? Thanks in advance

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi febrina ,

     

    You can create a measure with below code:-

     

    AVERAGE =
VAR _num = [Measure1] + [Measure2] + [Measure3] + [Measure4] + [Measure5] + [Measure6] + [Measure7] + [Measure8]
VAR _den =
    IF ( ISBLANK ( [Measure1] ), 0, 1 ) + IF ( ISBLANK ( [Measure2] ), 0, 1 )
        + IF ( ISBLANK ( [Measure3] ), 0, 1 )
        + IF ( ISBLANK ( [Measure4] ), 0, 1 )
        + IF ( ISBLANK ( [Measure5] ), 0, 1 )
        + IF ( ISBLANK ( [Measure6] ), 0, 1 )
        + IF ( ISBLANK ( [Measure7] ), 0, 1 )
        + IF ( ISBLANK ( [Measure8] ), 0, 1 )
RETURN
    Divide(_num , _den)

     

     

    Thanks,

    Samarth

    • febrina's avatar
      febrina
      New Member
      4 years ago

      Hi, samarth_18!

      It's worked, thankyou so much!