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

 

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

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

      harshagraj 

      please provide the sample data of this.