topic Re: How can I calculate running percentile in DAX? in DAX Commands and Tips

How can I calculate running percentile in DAX?

austin_infinite — Mon, 09 May 2022 19:12:27 GMT

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!

Re: How can I calculate running percentile in DAX?

tamerj1 — Mon, 09 May 2022 19:22:26 GMT

Hi austin_infinite

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

Re: How can I calculate running percentile in DAX?

austin_infinite — Mon, 09 May 2022 20:01:06 GMT

Those are all DAX Measures.

Re: How can I calculate running percentile in DAX?

tamerj1 — Tue, 10 May 2022 03:05:56 GMT

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] )

Re: How can I calculate running percentile in DAX?

Anonymous — Thu, 12 May 2022 02:49:44 GMT

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