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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

May 2025 Monthly Update

Fabric Community Update - May 2025

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