Convert Rank to Customer Count

[etane]

Hello.

I've been trying to figure out how many customers are buying X% of our products.

In my work in progress file (linked below), I've figured out those customers that purchased 25% of our products are ranked 1 thru 37.  However, because there are ties, there's actually 41 customers in this cohort.  But, I don't know how to write a DAX that shows 41 instead of 37.  

So, because I figured out the rank, I need either a DAX that can:

1) Calculate N customers less than or equal to X% of total

or

2) Calcualte N customers at less than or equal to Y rank including ties

Link

Thanks!

[etane · Accepted solution]

Figured it out.  Actually it wasn't that hard:

[lbendlin]

Any particular reason for not using PERCENTILEX for this?

[etane]

Yup.  I never even heard of that function before....

[lbendlin]

I would recommend starting with that PERCENTILEX.INC function (DAX) - DAX | Microsoft Learn - it will make your request much easier to implement.

[etane]

After watching a few videos, I am not sure how to apply the function to my dataset.

If I knew how to apply the percentile function onto my dataset after it's summarized, it can tell me what my units distribution are.  However, I already have that albeit not using the percentile function.  Further, I don't know how to use summarize and percentile function together, so the result is always 1, because each row of data holds only 1 unit.  

What I need is not a unit distribution but the # of customers who purchased up to a percentile's threshold.

[lbendlin]

1. identify the products for each percentile

2. lookup the customers who purchased these products ( use the result of 1 as a filter)

[etane]

I already did that in my test file.  However, I need that in a stand alone dax without using filters.

[lbendlin]

won't work without filters, but these filters can come from table variables. Something along these lines:

var x25 = percentilex.inc(products,0.25)

return countrows(filter(values(customer name), product in x25))

[etane]

with my data set this won't work.  every row is one unit so no matter what % you use the distribution will be one.