Forum Discussion

harshagraj's avatar
harshagraj
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

To calculate Index on one column

Hi all, I have below data and I need measure calcualtion to achieve skill index on Column USER_DN level that is 1.33.
Index calculations = Average(Final_User_Rating)/Average(MANAGER_EXPECTATIONS)
I need to achieve 1.33 [(2+1+1)/(1+1+1)]


USER_DNCOMPETENCY_L3_NAMEFinal_User_RatingMANAGER_EXPECTATIONSIndex
AbdTableau212
AbdPower BI111
AbdExcel111
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi harshagraj ,

    So in your table the index result should be (2+1+2)/(1+1+2) = 1.25 right?

    If so, try like this measure:

    Index =
VAR _ra =
    CALCULATE (
        SUM ( 'SA USER_RATING'[Final_User_Rating] ),
        ALLEXCEPT ( 'SA USER_RATING', 'SA USER_RATING'[USER_DN] )
    )
VAR _ex =
    CALCULATE (
        SUM ( 'SA USER_RATING'[MANAGER_EXPECTION] ),
        ALLEXCEPT ( 'SA USER_RATING', 'SA USER_RATING'[USER_DN] )
    )
RETURN
    DIVIDE ( _ra, _ex, BLANK () )

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    harshagraj try this measure

     

    Avg Measure = 
DIVIDE ( SUM ( Table[Final User Rating] ), SUM ( Table[Manager Expectations] ) )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • harshagraj's avatar
      harshagraj
      Icon for Post Partisan rankPost Partisan
      6 years ago

      Hi parry2k thank you for replying. I tried this 

      SA User LOD = DIVIDE(SUM('SA USER_RATING'[Final_User_Rating]),SUM('SA USER_RATING'[MANAGER_EXPECTATIONS]))
      This works when i take the user only but when i put the competancy it splits. I need to agg on User level. Below is the screen shot.
      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        6 years ago

        harshagraj 

        you can add ALL function to get the result

        Measure 2 = 
var rating=CALCULATE(sum('Table (3)'[RATING]),all('Table (3)'))
var expectation=CALCULATE(sum('Table (3)'[EXPECTATION]),ALL('Table (3)'))
return DIVIDE(rating,expectation)