DAX to calculate the percentile group dynamically

[ylin88_waters]

Hi
I have a column "SalesPotential" with a value for each customer (one row one customer). I want to create a measure to assign all customers to four groups based on their percentiles of SalesPotential, like ">75th" for those SalesPotential on the top 25%, ">50th" for those >=50% and <75%, and so on. I want to keep all filters in this calculation. For example, if I have a slicer on country and users select "USA", this percentile group will be only for the customers in USA.

I am new to DAX and tried the following but failed:

Potential Percentile=
var P75 =PERCENTILEX.INC( [Potential], 0.75 )
var P50 =PERCENTILEX.INC( [Potential], 0.50 )
var P25 =PERCENTILEX.INC( [Potential], 0.25 )
SWITCH(TRUE(),
[Potential] >= P75, ">= 75th Percentile",
[Potential] >= P50, ">= 50th Percentile",
[Potential] >= P25, ">= 25th Percentile",
"<25th Percentile" )

After I typed "TRUE(),", Power BI only popped out measures for me, no columns so I can't get the column "Potential" I need. I think I did something wrong. Can anybody help on this DAX? Thanks a lot!!

[Greg_Deckler · Accepted solution]

@ylin88_waters If this is a measure then you are going to need to wrap an aggregation around your column references like MAX or MIN. So:

var P75 =PERCENTILEX.INC( [Potential], 0.75 )
var P50 =PERCENTILEX.INC( [Potential], 0.50 )
var P25 =PERCENTILEX.INC( [Potential], 0.25 )
SWITCH(TRUE(),
MAX([Potential]) >= P75, ">= 75th Percentile",
MAX([Potential]) >= P50, ">= 50th Percentile",
MAX([Potential]) >= P25, ">= 25th Percentile",
"<25th Percentile" )