Average per category

[Anonymous]

Hi I have data in the following form along with the expected output column (section_1 average per id):

Id	QuestionID	score	section	section_1 average per id
1	a	2	1	(2+1)/2=1.5
1	b	1	1	(2+1)/2=1.5
1	c	3	2	3/1=3
2	a	3	1	(3+2)/2=2.5
2	b	2	1	(3+2)/2=2.5
2	c	1	2	1/1=1

Each question belongs to a section. I would like to use DAX to calculate the average score section for each id in the table (end column above).  

I have tried using SELECTCOLUMNS(CALCULATETABLE(data, data[Section]=1, data[ID]=EARLIER(data[ID])),"Score",[Score]) to filter out the single column I need but AVERAGE won't accept a table expression like this. 

Can anyone help?

TIA

[harshnathani · Accepted solution]

Hi @Anonymous ,

Create a Calculated Column

Section Average =

var _a = CALCULATE(SUM('Table'[score]), FILTER('Table','Table'[Id] = EARLIER('Table'[Id]) && 'Table'[section] = EARLIER('Table'[section])))

var _b = CALCULATE(COUNT('Table'[score]), FILTER('Table','Table'[Id] = EARLIER('Table'[Id]) && 'Table'[section] = EARLIER('Table'[section])))

RETURN

DIVIDE(_a,_b)

Change formatting of Section Average Column to Decimal

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)