Get orders from top 10% of customers

[will_lamslecz]

Hello, 
I am trying to create a measure that returns the number of orders from the customers who make up the top 10% of sales. For example, if total sales is $100, and the top 4 customers make up $10 of sales, and those 4 customers have a collective 20 orders, it would return 20. 
I'd like to know how I could go about doing this.

[Anonymous]

Hi @will_lamslecz ,

Jihwan_Kim said it very well, his method is very good.

You can try my different method.

Please follow my steps below:

First, here is the sample data I created, which are the Sales table and the Order table.

Second, create a calculated column that ranks customers based on their sales.

Customer Sales Rank = RANKX(ALL(Sales), Sales[Sales], , DESC, Dense)

Finally, create a calculated column to take out the information of the first four customers.

If it is the first four customers, it is equal to true, if not, it is equal to false.

Is Top Customer = IF(Sales[Customer Sales Rank] IN { 1,2,3,4}, TRUE, FALSE)

Apply the measure to a visual-level Filter that only displays True.

pbix file is attached.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

Please be noted that in my sample, I tried to find top 50 % customers order count.

Sales: = 
    SUM( sales[sales] )

Order count: = 
    COUNTROWS(sales)

WINDOW function (DAX) - DAX | Microsoft Learn

top % sales: = 
VAR _allsales =
    CALCULATE ( [Sales:], REMOVEFILTERS ( customer ) )
VAR _runningsalesdesc =
    CALCULATE (
        [Sales:],
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( customer[customer_id] ),
            ORDERBY ( [Sales:], DESC )
        )
    )
VAR _percentage =
    DIVIDE ( _runningsalesdesc, _allsales )
RETURN
    _percentage

Top 50% customer ordercount = 
VAR _customerlist =
    FILTER ( VALUES ( customer[customer_id] ), [top % sales:] <= 0.5 )
RETURN
    CALCULATE (
        [Order count:],
        FILTER ( customer, customer[customer_id] IN _customerlist )
    )