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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
v-jayw-msft
Community Support
Community Support

Hi @joshuar ,

 

Please share some sample data and expected result.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hi Jay,

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.