Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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.
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.
Hello and thank you for the help on this! I am getting a value of 5 for all of the customers in the table?
Hello ,
I am geting all 5s for the F Score for all customer ID?
Thanks for your support!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |