Calculating PERCENTILE in DAX

Question

I have googled and keep ending up with formulas which are too slow. I suspect if I split the formula in steps (creating calculated columns), I might see some performance gain.

I have a table having some numeric columns along with some which would end up as slicers. The intention is to have 10th, 25th, 50th, 75th and 90th percentile over some numeric columns for the selected slicer.

This is what I have for the 10th Percentile over the column "Total Pd".

TotalPaid10thPercentile:=MINX(
                              FILTER(
                                     VALUES(ClaimOutcomes[Total Pd]),
                                     CALCULATE(
                                               COUNTROWS(ClaimOutcomes),
                                               ClaimOutcomes[Total Pd] <= EARLIER(ClaimOutcomes[Total Pd]) 
                                              )> COUNTROWS(ClaimOutcomes)*0.1
                                    ),
                               ClaimOutcomes[Total Pd]
                             )

It takes several minutes and still no data shows up. I have around 300K records in this table.

Suggest you review the information on Marco's site here, it's got a good example of percentile in DAX. http://www.daxpatterns.com/statistical-patterns/#skip-to-complete-pattern — Lukasz P., Jun 12 '15 at 15:27

score 2 · Accepted Answer · answered Jun 23 '15 at 03:37

I figured out a way to break the calculation down in a series of steps, which fetched a pretty fast solution.

For calculating the 10th percentile on Amount Paid in the table Data, I followed the below out-of-the-book formula :

Calculate the Ordinal rank for the 10th percentile element

10ptOrdinalRank:=0.10*(COUNTX('Data', [Amount Paid]) - 1) + 1

It might come out a decimal(fraction) number like 112.45

Compute the decimal part

10ptDecPart:=[10ptOrdinalRank] - TRUNC([10ptOrdinalRank])

Compute the ordinal rank of the element just below(floor)

10ptFloorElementRank:=FLOOR([10ptOrdinalRank],1)

Compute the ordinal rank of the element just above(ceiling)

10ptCeilingElementRank:=CEILING([10ptOrdinalRank], 1)

Compute element corresponding to floor

10ptFloorElement:=MAXX(TOPN([10ptFloorElementRank], 'Data',[Amount Paid],1), [Amount Paid])

Compute element corresponding to ceiling

10ptCeilingElement:=MAXX(TOPN([10ptCeilingElementRank], 'Data',[Amount Paid],1), [Amount Paid])

Compute the percentile value

10thPercValue:=[10ptFloorElement] + [10ptDecPart]*([10ptCeilingElement]-[10ptFloorElement])

I have found the performance remarkably faster than some other solutions I found on the net. Hope it helps someone in future.

Calculating PERCENTILE in DAX

1 Answers1