Using Percenitles Based On Variable

[joshuar]

I am trying to do a percentile calculation based on what customer segment(group) the customer are in. I have a column that corresponds to the customer 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")

[joshuar]

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

[Anonymous]

Hi @joshuar ,

Please share some sample data and expected result.

Best Regards,

Jay

[joshuar]

Hi Jay,

I posted sample data nd sample result. Thanks for your help!