Forum Discussion

Post 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

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

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