Percentile in column

[basrooz]

Hi,

I have a table with clients, the amount of orders, and the average amount of orders per month.

Client ID	Country	Orders	Average orders per month
A	USA	100	3
B	USA	10	5
C	Canada	50	8

I want to add a calculated column that shows the percentile based on average orders per month. Like this:

Client ID	Country	Orders	Average orders per month	Percentile
A	USA	100	3	10th
B	USA	10	5	20th
C	Canada	50	8	70th

I’d like to split things up in the following percentiles: 10, 20, 30, 40, 50, 60, 70, 80, 85, 90, 95, 99 .

I’ve managed to create a measure using the percentile.exc formula which calculates the percentile based on average orders per month, but I now want to have it in the table as a calculated column. Reason for this is that I want to see for each percentile how many clients and how many orders we’re talking about.

Would you know how to fix this?

Best regards

Bas

[v-yueyunzh-msft]

Hi, @basrooz 

Based on your description, you want to create two calculated columns. Right?

For your needs, I'm sorry, I don't understand your Percentile's computational logic.

If you want to group based on Country, you can use FILTER() in combination with the EARLIER() function to implement grouping in a calculated column.

You can describe your calculation logic in detail, or you can provide us with your DAX formula so that we can better help you.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly