Forum Discussion

ArashZ's avatar
ArashZ
Helper I
5 years ago

Average based on some specific rows

Hi,

 

I have a table like this: Each user ID has the course name of base and advanced and the duration is the same for each user based on their course name. In fact, for user A, the duration for the base is 36, and for advanced is 27, but it has been written in multiple rows. I want to get an average of duration separately for the base, advanced, and for both together. For instance, the average for the base should be (36,30,20) and the average for advanced is (27,10,15), and for both is (36,27,30,10,20,15).

 

user IDCourse nameStart finish duration
ABase36
ABase36
AAdvanced27
AAdvanced27
AAdvanced27
BBase30
BBase30
BAdvanced10
CBase20
CBase20
CBase20
CAdvanced15
CAdvanced15
CAdvanced15
CAdvanced15

4 Replies

    • ArashZ's avatar
      ArashZ
      Helper I
      5 years ago

      CNENFRNLThanks. But is there a way to write a calculated column or measure for that?

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        5 years ago
        Avg = 
SUMMARIZECOLUMNS(
    COURSE[user ID],
    COURSE[Course name],
    "Avg", AVERAGEX( COURSE, COURSE[Start finish duration] )
)