CALCULATING an AVG Measure for category segment

[abertolett]

 Having a hard time calculating an AVG just for records where they share another column's value (e.g. same category).

Here's a simplification of the model:

AOV is simply Rev/Orders

Here's the measure I tried:

Category AOV= CALCULATE(SUM(Table B[Revenue]) / SUM('Table A'[Orders]), ALLEXCEPT('Table A','Table A'[Orders]))

What's strange is that it seemed to work where "Category AOV" was matching category. Then I changed a visualization and then every "Category AOV" was the same value.

Obviously there's something I'm missing about how Measures interact with filtering.

[Anonymous]

@abertolett

An other way is to change the context by using this formula:

Category AOV = 
CALCULATE(
	sum('Table B'[Revenue]);
	ALL('Table A'[Name])) 
	/ 
CALCULATE(
	sum('Table A'[Orders]);
		All('Table A'[Name]) 
		)

[v-sihou-msft · Accepted solution]

@abertolett

In this scenario, since you want to calculate the AOV by Category, you should put Category column in ALLEXCEPT() to have your calculation group on this column.

AOV = CALCULATE(SUM(TableB[Rev])/SUM(TableA[Orders]),ALLEXCEPT(TableA,TableA[Category]))

Regards,