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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
joshuar
Frequent Visitor

Using Percenitles Based On Variable

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")
3 REPLIES 3
joshuar
Frequent Visitor

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
Anonymous
Not applicable

Hi @joshuar ,

 

Please share some sample data and expected result.

 

Best Regards,

Jay

Hi Jay,

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.