Using Percentiles Based On Column

[joshuar]

I am trying to do a percentile calculation based on what customer segment(group) that a customer is in. I have a column that corresponds to the customer ID and reflects what segment they are in.

Here is the formula I have but need to calculate the perenctile based on a customers segmentation which is stored in a column called Customer Group. I want to do this percentile caluclation based on the group a custome rbelongs to and not the entire dataset:

F Score = SWITCH(

TRUE(),

[F Value]<=PERCENTILE.INC('RFM Table'[F Value], 0.20 ), "1",

[F Value]<=PERCENTILE.INC('RFM Table'[F Value], 0.40 ), "2",

[F Value]<=PERCENTILE.INC('RFM Table'[F Value], 0.60 ), "3",

[F Value]<=PERCENTILE.INC('RFM Table'[F Value], 0.80 ), "4", "5")

Here is the sample data:

This reflects Customer ID, the Group the customer belongs to and the # of Orders. The F value in the dax formula I have is the # of Orders.

Customer ID	Customer Group	# of Orders
706104	C	3
994195	C	2
386615	A	1
235864	A	5
922887	B	9
567869	C	7
494345	C	9
606611	C	1
184412	A	0
357310	A	8
285382	B	1
547244	C	3
75757	C	5
935099	C	8
685859	A	8
741397	A	9

I want to rank the customer based on the group they belong to, and not the entire data set. We have many customer groups and I want to rank customers based on the group they belong to. 

Here is a result sample

Customer ID	Customer Group	# of Orders	Perecntile Rank
706104	C	3	3
994195	C	2	1
567869	C	7	2
494345	C	9	5
606611	C	1	1
547244	C	3	2
75757	C	5	4
935099	C	8	5

Here is another result samples

Customer ID	Customer Group	# of Orders	Perecntile Rank
386615	A	1	1
235864	A	5	3
184412	A	3	2
357310	A	8	4
685859	A	8	4
741397	A	9	5

[v-zhangti · Accepted solution]

Hi, @joshuar 

First of all, the output result you gave about C should be wrong, correct as shown in the figure.

Second, if you want to calculate by group, you can change the formula PERCENTILEX.INC.

PERCENTILEX.INC function (DAX) - DAX | Microsoft Learn

Measure = SWITCH(
TRUE(),
MAX('RFM Table'[F Value])<= PERCENTILEX.INC(FILTER(ALL('RFM Table'),[Customer Group]=SELECTEDVALUE('RFM Table'[Customer Group])),[F Value],0.2), "1",
MAX('RFM Table'[F Value])<= PERCENTILEX.INC(FILTER(ALL('RFM Table'),[Customer Group]=SELECTEDVALUE('RFM Table'[Customer Group])),[F Value], 0.40 ), "2",
MAX('RFM Table'[F Value])<= PERCENTILEX.INC(FILTER(ALL('RFM Table'),[Customer Group]=SELECTEDVALUE('RFM Table'[Customer Group])),[F Value], 0.60 ), "3",
MAX('RFM Table'[F Value])<= PERCENTILEX.INC(FILTER(ALL('RFM Table'),[Customer Group]=SELECTEDVALUE('RFM Table'[Customer Group])),[F Value],0.80 ), "4", "5")

Best Regards,

Community Support Team _Charlotte

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

[joshuar]

Hello and thank you for the help on this! I am getting a value of 5 for all of the customers in the table?

[joshuar]

Hello , 

I am geting all 5s for the F Score for all customer ID?

Thanks for your support!