Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joshuar
Frequent Visitor

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:

 

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 IDCustomer Group# of Orders
706104C3
994195C2
386615A1
235864A5
922887B9
567869C7
494345C9
606611C1
184412A0
357310A8
285382B1
547244C3
75757C5
935099C8
685859A8
741397A9

 

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 IDCustomer Group# of OrdersPerecntile Rank
706104C33
994195C21
567869C72
494345C95
606611C11
547244C32
75757C54
935099C85

 

 

Here is another result samples

 

Customer IDCustomer Group# of OrdersPerecntile Rank
386615A11
235864A53
184412A32
357310A84
685859A84
741397A95
1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @joshuar 

 

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

vzhangti_0-1666859628167.png

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")

vzhangti_2-1666859845132.pngvzhangti_3-1666859865690.png

 

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.

View solution in original post

3 REPLIES 3
v-zhangti
Community Support
Community Support

Hi, @joshuar 

 

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

vzhangti_0-1666859628167.png

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")

vzhangti_2-1666859845132.pngvzhangti_3-1666859865690.png

 

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?

 

joshuar_0-1667392997559.png

 

Hello , 

 

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

 

Thanks for your support!

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.