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 formul...
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
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
  • markostalnacke's avatar
    markostalnacke
    Frequent Visitor
    3 years ago

    Thank you so much, worked perfectly!