Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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]) )
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
50 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |