Forum Discussion
Filtering with Percentile.INC
[Score 45] = PERCENTILE.INC ( T[Score], .45 ) [Score 65] = PERCENTILE.INC ( T[Score], .65 ) [Score 85] = PERCENTILE.INC ( T[Score], .85 ) -- or [Score 45] = PERCENTILE.EXC ( T[Score], .45 ) [Score 65] = PERCENTILE.EXC ( T[Score], .65 ) [Score 85] = PERCENTILE.EXC( T[Score], .85 )
For the differences in the definition of the functions used, please see https://dax.guide/percentile-inc/ or https://dax.guide/percentile-exc/.
The above are MEASURES that will work with any filters that you put on the T table.
Best
Darek
I'm trying to avoid measures because I have 85 units each with 36 different scores I need to calculate 3 percentiles for. That would be close to 9,000 measures. Is there a way to do it with a column or variables so I can do it across a whole sheet of data and only look at one specific unit at a time?
Who says 9000 measures? You clearly don't understand how the measures I wrote work. You only need 3 measures for the whole setup to do what you want... Jesus... :(
My advice is to learn something about DAX and Power BI.
Best
Darek
I'm trying to....
I understand you showed three measures and said that it would work for any filters. The problem I am trying to solve has 85 units and there are 36 contaminants per unit. I need to be able to filter through all the units individually and find the percentiles associated with each contaminent (score). I have 85 units each with 36 contaminants and I need 3 percentiles for each so 85*36*3 is just under 9,000.
Maybe you could explain the measure a little more to me then and that could be what I am looking for?
You can put all your units on the row axis of a matrix, then all the contaminants on the column axis and then drop the measures onto the matrix. It'll display the percentiles for any combination of ( unit X contaminant ).
What's the problem here? I can't see any... The matrix can be filtered for particular units and contaminants with slicers as well to limit the data to display.
Best
Darek