Forum Discussion
Filtering with Percentile.INC
I have two tables I am working with. One has multiple scores for multiple units (table 1) and the other is going to be a summary table with some other caclulations and will be unit specific (table 2). Like this
Table 1Table 2
I want to be able to find the 45th, 65th, and 85th, percentiles for each unit from Table 1 and put the value in Table 2. Is there a way to set up a calculated column to find the given percentile from the scores for a particular unit in Table 1 and store it in Table 2. For example if unit 401 had a 45th percentile of 70 I would want a value of 70 placed in B2 on table 2. (I know I show excel tables instead of power bi tables, but the visual is the same).
I only have two units in the pictures but I will end up needing to do it for about 100 units each with around 100 samples, so I really don't want to have to make 3 measures for each unit.
Any help would be appreciated, thank you!
7 Replies
[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?