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

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

Hi ryan_mayu thanks for responding. Unfortunately it is not working,. If we just take the data seperately as above then it works..but in actual scenario it doesn't.
I am calculating Skill Index based on Final User Rating Column and Manager Expectation. 
Final User Rating column is again a calculated column. 

Final_User_Rating =
IF('SA USER_RATING'[MANAGER_ADJUSTMENTS]=0||ISBLANK('SA USER_RATING'[MANAGER_ADJUSTMENTS]),
'SA USER_RATING'[USER_RATINGS],
'SA USER_RATING'[MANAGER_ADJUSTMENTS])
With the solution u provided i am getting 0.21 as Skill Index.
SA User LOD =
Var a = CALCULATE(SUM('SA USER_RATING'[Final_User_Rating]),ALL('SA USER_RATING'))
Var b = CALCULATE(SUM('SA USER_RATING'[MANAGER_EXPECTATIONS]),ALL('SA USER_RATING'))
Return
DIVIDE(a,b)




v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
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.