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
CarterB
New Member

Percentiles based on group summations

Hello,

 

I'm borrowing from a previous unanswered question found in the link below:

 

http://community.powerbi.com/t5/DAX-Commands-and-Tips/percentile-group-by-multiple-categories/td-p/7...

 

 

I have a similarly structured table that has data that looks like the one below. I'm looking for percentile that is dynamic (I'm thinking it's going to have to be a measure) based on a filter or set of filters (was thinking ALLSELECTED would have to be used but I'm unsure).

 

I need to see the 50th Percentile Lead time by quarter and/or by site dynamically in a visual. I did look into the percentile visual but could not get it to work the way i require it to work. The Materials are unique to a site, so that is not a concern. 

 

I would have to see 50th Percentile Lead time by site, 50th percentile lead time by quarter and then see 50th percentile lead time by quarter and site together.

 

If this data were to extend to 2016Q3 and 2016 Q4, would it be possible to treat the values compared in percentile as the sum of each quarter?

 

Please help!

 

SiteQuarterMaterialLead Time
Italy2016Q1POL1125
Italy2016Q1POL2110
Malaysia2016Q1POL390
Malaysia2016Q1POL487
Japan2016Q1EPI1110
Japan2016Q1EPI298
Italy2016Q2POL5115
Italy2016Q2POL6100
Malaysia2016Q2POL780
Malaysia2016Q2EPI377
Japan2016Q2POL8100

 

Edit:

 

I believe I'm looking for CALCULATE(SUM(CALCULATE(PERCENTILE(TABLE[Lead Time]),.5), ALLSELECTED(Table[MATERIAL]))

 

but the sum function only accepts a column reference as an argument.

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@CarterB,

 

You may try PERCENTILEX.INC Function.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.