Forum Discussion

CarterB's avatar
CarterB
New Member
8 years ago

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/77959

 

 

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.