Forum Discussion
Help with Average formula with filtering
- 3 years ago
Without knowing the data model, data samples, and what is the output expected, it is tough to advice. For me, it sounds like you are doing average by group and your formula is correct in my view:
Anes Start Average =
CALCULATE(
AVERAGE('Case Details'[Anes Start]),
ALLEXCEPT('Case Details',
'Case Details'[Surgeon],'Case Details'[Facility],'Case Details'[OR])
)
Quick check tip: add and delete this table to verify
Assuming you have in the data model, a relation between Calendar Table and Case Details.
1. Click "New Table" in the modeling and add table via DAX 2. Dax formula New Table = SUMMARIZE( 'Case Details' , 'Case Details'[Surgeon] , 'Case Details'[Facility] , 'Case Details'[OR] , 'Calendar Table'[Month & Year] , "Rows Count 1", Count('Case Details'[Anes Start]) , "Avg. Test 1", AVERAGEX('Case Details', 'Case Details'[Anes Start])) , "Avg. Test 2", AVERAGE('Case Details'[Anes Start]) )Let us know if you see the results the same way you want?
Without knowing the data model, data samples, and what is the output expected, it is tough to advice. For me, it sounds like you are doing average by group and your formula is correct in my view:
Anes Start Average =
CALCULATE(
AVERAGE('Case Details'[Anes Start]),
ALLEXCEPT('Case Details',
'Case Details'[Surgeon],'Case Details'[Facility],'Case Details'[OR])
)
Quick check tip: add and delete this table to verify
Assuming you have in the data model, a relation between Calendar Table and Case Details.
1. Click "New Table" in the modeling and add table via DAX
2. Dax formula
New Table = SUMMARIZE( 'Case Details'
, 'Case Details'[Surgeon]
, 'Case Details'[Facility]
, 'Case Details'[OR]
, 'Calendar Table'[Month & Year]
, "Rows Count 1", Count('Case Details'[Anes Start])
, "Avg. Test 1", AVERAGEX('Case Details', 'Case Details'[Anes Start]))
, "Avg. Test 2", AVERAGE('Case Details'[Anes Start])
)
Let us know if you see the results the same way you want?