cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Help with Average formula with filtering

Hello,

I have the fields, Surgeon, Date, Start Time, End Time, Facility, OR.....I would like to find a surgeons average start and end times, overall, but then also be able to find it by Day of Week, Facility, OR, if adding these fields to the visual.  I have the formula:

Anes Start Average = CALCULATE(AVERAGE('Case Details'[Anes Start]), ALLEXCEPT('Case Details','Case Details'[Surgeon]))

Which works if I just display a table with the Surgeon name, and their averages.  If I add additional fields like Day of Week, or Facility, these averages do not change. So I altered the formula to something like:

Anes Start Average = CALCULATE(AVERAGE('Case Details'[Anes Start]), ALLEXCEPT('Case Details','Case Details'[Surgeon],'Case Details'[Facility],'Case Details'[OR]))

Which doesn't work if I just want to display each surgeon and their average start and end times (there are multiple rows if they are linked to multiple facilities, etc)....Don't know how to fix it.

1 ACCEPTED SOLUTION
Super User

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?

3 REPLIES 3
Super User

Hi,

Share some data to work with (in a format the can be pasted in an MS Excel file.) and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @aashton,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors