cancel
Showing results for
Did you mean:

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

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
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 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
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.
Frequent Visitor

Hi Jay,

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors