Forum Discussion
Using Percentiles Based On Column
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:
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 |
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.
3 Replies
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.
