Forum Discussion

markostalnacke's avatar
markostalnacke
Frequent Visitor
3 years ago
Solved

HELP Converting index calculation into measure

Hi, 

 

I have a dataset which I can modify if needed. Right now it contains
userid, managerid, visit_count, manager_count

 

I need to calculate an indexvalue per userid based on following formula:
visit_count per manager exponent 2 + visit_count per manager exponent 2 +.. minus visit_count 
divided by

visit_count * visit_count-1

 

So some userid:s have one row and some might have 20 rows.. 

 

Any ideas how to get started?

Thanks in advance.

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    You can try

    UserID index =
VAR VisitsPerManager =
    ADDCOLUMNS (
        VALUES ( 'Table'[Manager ID] ),
        "@visits", CALCULATE ( SUM ( 'Table'[Visit count] ) )
    )
VAR TotalVisitsPerManager =
    SUMX ( VisitsPerManager, POWER ( [@visits], 2 ) )
VAR TotalVisitCount =
    SUM ( 'Table'[Visit count] )
VAR Result =
    DIVIDE (
        TotalVisitsPerManager - TotalVisitCount,
        TotalVisitCount * ( TotalVisitCount - 1 )
    )
RETURN
    Result

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You can try

    UserID index =
VAR VisitsPerManager =
    ADDCOLUMNS (
        VALUES ( 'Table'[Manager ID] ),
        "@visits", CALCULATE ( SUM ( 'Table'[Visit count] ) )
    )
VAR TotalVisitsPerManager =
    SUMX ( VisitsPerManager, POWER ( [@visits], 2 ) )
VAR TotalVisitCount =
    SUM ( 'Table'[Visit count] )
VAR Result =
    DIVIDE (
        TotalVisitsPerManager - TotalVisitCount,
        TotalVisitCount * ( TotalVisitCount - 1 )
    )
RETURN
    Result