topic Re: Get orders from top 10% of customers in DAX Commands and Tips

Get orders from top 10% of customers

will_lamslecz — Mon, 18 Mar 2024 20:49:56 GMT

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.

Re: Get orders from top 10% of customers

Jihwan_Kim — Mon, 18 Mar 2024 21:22:39 GMT

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 ) )

Re: Get orders from top 10% of customers

Anonymous — Tue, 19 Mar 2024 05:21:31 GMT

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!