Average for Unique IDs per category and Total

[filarap]

Hi Community,

I am trying to create a measure that will calculate average for each unique case per category and for total, for the unpivoted table that has one row per category for each case (one case has 5 rows, but only some of those rows are applicable).

I would appreciate any guidance to how to achieve this. Currently I have this formula active:

AVERAGEX(SUMMARIZE(table,table[Case ID]),AVERAGE(table[CSAT])))

Below is unpivoted Raw data.
Applicable row needs to be used for category based calculation.

Case ID	Category	Applicable Row	CSAT
1	Wine	1	7
1	Beer	1	7
1	Spirit	0	7
1	Juice	0	7
1	Water	0	7
2	Wine	0	9
2	Beer	0	9
2	Spirit	1	9
2	Juice	0	9
2	Water	0	9
3	Wine	1	3
3	Beer	1	3
3	Spirit	1	3
3	Juice	1	3
3	Water	1	3
4	Wine	1	5
4	Beer	0	5
4	Spirit	0	5
4	Juice	0	5
4	Water	1	5
5	Wine	1	10
5	Beer	0	10
5	Spirit	0	10
5	Juice	1	10
5	Water	0	10

Here is the output i need. I am able to get either correct calcualtion for category or for total, but not both ion the same visual (Matrix/table).

Category	avg CSAT
Beer	5
Juice	6.5
Spirit	6
Water	4
Wine	6.25
Total	6.8

[filarap · Accepted solution]

Hey

Managed to achieve it with this

Measure = CALCULATE(AVERAGE('Table'[CSAT]),FILTER(SUMMARIZE('Table','Table'[Case ID],"maxi",max('Table'[Applicable Row])),[maxi]=1))

Thanks all who took the time to look into this!!!

[amitchandak]

@filarap , Try like

AVERAGEX(Values(table[Case ID]),calculate(AVERAGE(table[CSAT])))

[filarap]

Hey @amitchandak , thanks for the reply.

Unfortunatelly, it didnt work.

Got total in all rows (as below).

Category	avg CSAT
Beer	6.8
Juice	6.8
Spirit	6.8
Water	6.8
Wine	6.8
Total	6.8