Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

MEDIANX and SUMMARIZE - need to exclude 0

Hello All!

 

Median =

MEDIANX(SUMMARIZE(GData,GData[Account],
                            "Contacts",CALCULATE(SUM(GData[TotalContacts]),FILTER(GData,GData[TotalContacts] <>  0  )),"FTE", CALCULATE(AVERAGE(GData[L1FTECount]),FILTER(GData,GData[L1FTECount] <> 0 ))),DIVIDE([Contacts],[FTE]))

It seems that it is still including zeros in the median which is why I am still getting the wrong median number. I hope someone can help with how to eliminate 0 in this measure.

Thanks!
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Try like

MEDIANX(filter(SUMMARIZE(GData,GData[Account],
"Contacts",SUM(GData[TotalContacts])
,"FTE", AVERAGE(GData[L1FTECount])),[Contacts] <> 0 && [FTE]<>0)
,DIVIDE([Contacts],[FTE]))

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

This works like a charm. Thank you!

amitchandak
Super User
Super User

@Anonymous , Try like

MEDIANX(filter(SUMMARIZE(GData,GData[Account],
"Contacts",SUM(GData[TotalContacts])
,"FTE", AVERAGE(GData[L1FTECount])),[Contacts] <> 0 && [FTE]<>0)
,DIVIDE([Contacts],[FTE]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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