Forum Discussion
Average per category
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
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])))RETURNDIVIDE(_a,_b)Change formatting of Section Average Column to DecimalRegards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
1 Reply
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])))RETURNDIVIDE(_a,_b)Change formatting of Section Average Column to DecimalRegards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)