DAX - Average only on Distinct Rows and with Year filter

[jayasurya_prud]

Hi, I am working on DAX Query where I need to calculate the Average only from the distinct rows and with filter

For example, this is my raw table

Name	Roll_no	Year	Marks
a	1	2014	93
a	1	2014	93
a	1	2014	93
b	2	2015	92
b	2	2014	91
b	2	2014	91
b	2	2015	94
c	3	2015	94
c	3	2016	80
c	3	2014	81
c	3	2014	81
c	3	2016	75
c	3	2017	76
d	4	2015	45
d	4	2016	46
d	4	2014	50
d	4	2014	50
d	4	2016	46
d	4	2017	76

from this, we could see that there are duplicates of same name with roll num in same year. My goal is to not to consider the duplicates while calculating the average of marks.

My end goal is to get an average of the year filter - 2014 with only distinct values

like this

Name	Roll_no	Year	Marks
a	1	2014	93
b	2	2014	91
c	3	2014	81
d	4	2014	50
avg			78.75

please help me to create the dax

[jayasurya_prud]

Hi, thanks for sharing, 

But how to apply year filter on this. I need to calculate only for 2014. How apply filter for year? 

And may I know why you have used marks column in the distinct? Shouldn't it be the name/roll num? 

[Mahesh0016]

[Mahesh0016]

If you want to show only 2014 Avg marks so you give visual level filter in filter by year to 2014. and why i use distinct marks column because of we want to avg of distinct mark so i have use column as a table .

[jayasurya_prud]

I got the filter part. 

But now, my conecern is the disctinct values. I need to consider the name / roll num column for taking distinct values right. I can have 2 students getting same marks. If I take marks as distinct, my result will go wrong, right? 

My aim is to consider the distinct values of name/roll num and calculate the average. 

The dax you shared is helping close to the results, But I am concerned about the Distinct part. Please help me

[Mahesh0016]

Avg Marks = AVERAGEX(DISTINCT(Student_Marks[Marks]),Student_Marks[Marks])

Hello @jayasurya_prud ,
Try Above Solution..... If it is helpful for you so please accept as Solution marks .

Thank You!