topic Re: Add percentile group per row as a calculated column with DAX? in DAX Commands and Tips

Add percentile group per row as a calculated column with DAX?

chillpill — Fri, 21 Mar 2025 19:30:32 GMT

I have a list of names and their data. I would like to add a column to show which person is in which grouping. I would like to group by 50%, 40% and 10% if that's posisble. I have tried with PERCENTILE.EXC but I am not getting the expected results. How would I accomplish this? Or would it need to be a measure instead of a calculated column?

Name	Data	Percentile
John Smith	\| 0.259309605	\| 25%
Mary Smith	\| 0.332877138	\| 25%
Mark Smith	\| 0.899507702	\| 50%
Jerry Smith	\| 0.805348293	\| 50%
Danny Smith	\| 0.267092547	\| 50%
Marg Smith	\| 0.272978056	\| 50%
Able Smith	\| 0.392131121	\| 10%
Mike Smith	\| 0.534344624	\| 10%
Brert Smith	\| 0.874071811	\| 25%
Hanny Smith	\| 0.872285147	\| 50%
Mick Smith	\| 0.508295626	\| 10%

Re: Add percentile group per row as a calculated column with DAX?

techies — Fri, 21 Mar 2025 19:56:06 GMT

Hi chillpill please try this

Percentile_Group_Measure =

VAR CurrentValue = MAX(Sheet3[Data])

VAR P10 = PERCENTILEX.INC(ALL(Sheet3), Sheet3[Data], 0.90)

VAR P50 = PERCENTILEX.INC(ALL(Sheet3), Sheet3[Data], 0.50)

RETURN

SWITCH(

TRUE(),

CurrentValue >= P10, "10%",

CurrentValue >= P50, "40%",

"50%"

)

Re: Add percentile group per row as a calculated column with DAX?

chillpill — Fri, 21 Mar 2025 20:02:02 GMT

Is this a calculated column or applied as a measure?

Re: Add percentile group per row as a calculated column with DAX?

techies — Fri, 21 Mar 2025 20:04:56 GMT

measure

Re: Add percentile group per row as a calculated column with DAX?

chillpill — Fri, 21 Mar 2025 20:21:43 GMT

Wonderful, thank you! If I wanted to have the measure dynamically update based on a slicer/filter - how would that be possible? I removed the ALL() function from your measure but that didn't change anything.

Re: Add percentile group per row as a calculated column with DAX?

techies — Fri, 21 Mar 2025 20:32:48 GMT

replace ALL with ALLSELECTED

Re: Add percentile group per row as a calculated column with DAX?

sevenhills — Fri, 21 Mar 2025 23:25:33 GMT

If you want it as a calculated column, same like chillpill solution, you can use this:

Percentile_Group_col = VAR CurrentValue = Table3[Data] VAR P10 = PERCENTILEX.INC( Table3 , Table3[Data], 0.90) VAR P50 = PERCENTILEX.INC( Table3 , Table3[Data], 0.50) RETURN SWITCH( TRUE(), CurrentValue >= P10, "10%", CurrentValue >= P50, "40%", "50%" )

output: