How can I calculate running percentile in DAX?

[austin_infinite]

Can't quite figure out how to calculate running percentile in DAX.

In Excel I used:

=+SUMIF([Rank],"<="&[@Rank],[Percentile])

Any help would be appreciated!

[Anonymous · Accepted solution]

Hi  @austin_infinite ,

You might consider creating a Rank using a computed column:

Here are the steps you can follow：

1. Create measure.

Measure =
SUMX(FILTER(ALL('Table'),'Table'[Rank]<=MAX('Table'[Rank])),[Percentile])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tamerj1]

Hi @austin_infinite 

Balance, Rank and Percentile, are these measures or columns in your source data?

[austin_infinite]

Those are all DAX Measures.

[tamerj1]

Hi @austin_infinite 

You are trying to calculate the moving average of a measure based on another measure. Usually this type of calcuation is heavy and is difficult to find an efficient solution. You may try the following. If not feasible then the options are either a calculated column or a calculated table. 

Running Percentile =
VAR CurrentRank = [Rank]
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( Data[Bill_to_Customer_No] ), [Rank] <= CurrentRank ),
        [Percentile]
    )