Forum Discussion
CALCULATING an AVG Measure for category segment
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.
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,
2 Replies
v-sihou-msftMicrosoft Employee
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,
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]) )
