Average based on some specific rows

[ArashZ]

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 ID	Course name	Start finish duration
A	Base	36
A	Base	36
A	Advanced	27
A	Advanced	27
A	Advanced	27
B	Base	30
B	Base	30
B	Advanced	10
C	Base	20
C	Base	20
C	Base	20
C	Advanced	15
C	Advanced	15
C	Advanced	15
C	Advanced	15

[CNENFRNL]

[ArashZ]

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

[PaulOlding]

If you want a measure then it'll need to create the equivalent of the Table.Distinct(Source) table on the fly.

Average Measure =

AVERAGEX(

SUMMARIZE('Table', 'Table'[Course name], 'Table'[user ID], 'Table'[Start finish duration]),

'Table'[Start finish duration]

)

[CNENFRNL]

Avg = 
SUMMARIZECOLUMNS(
    COURSE[user ID],
    COURSE[Course name],
    "Avg", AVERAGEX( COURSE, COURSE[Start finish duration] )
)