To calculate Index on one column

[harshagraj]

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_DN	COMPETENCY_L3_NAME	Final_User_Rating	MANAGER_EXPECTATIONS	Index
Abd	Tableau	2	1	2
Abd	Power BI	1	1	1
Abd	Excel	1	1	1

[parry2k]

@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]

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]

@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]

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 · Accepted solution]

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]

@harshagraj 

please provide the sample data of this.