topic Re: Top Customers in DAX Commands and Tips

Top Customers

Oros — Mon, 17 Jun 2024 02:44:36 GMT

Hello,

What would be the correct measure to show the Top 10 customer over a period of time? Thanks.

The resulting ranking is not based on the grand total but based on the total sales for a given month (or column period).

In the example below, if there are 500 customers, the measure will show only 16 customers that fall into the TOP 10 customers for the given period which is January to May (on a month-to-month basis)

Re: Top Customers

Jihwan_Kim — Mon, 17 Jun 2024 03:34:34 GMT

Hi,

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

In my sample, I tried to achieve top 3, and I hope you can reauthor the measure in your sample.

Please check the below picture and the attached pbix file.

Sales: = SUM( sales[sales])

WINDOW function (DAX) - DAX | Microsoft Learn

Sales top 3: = VAR _selectedperiod = ALLSELECTED ( 'calendar'[MOnth-Year sort] ) VAR _t = ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZE ( ALL ( sales ), customer[customer], 'calendar'[MOnth-Year sort], 'calendar'[Month-Year] ), 'calendar'[MOnth-Year sort] IN _selectedperiod ), "@sales", [Sales:] ) VAR _group = GROUPBY ( _t, customer[customer], "@allsales", SUMX ( CURRENTGROUP (), [@sales] ) ) VAR _toplist = SUMMARIZE ( WINDOW ( 1, ABS, 3, ABS, _group, ORDERBY ( [@allsales], DESC ) ), customer[customer] ) RETURN CALCULATE ( SUM ( sales[sales] ), KEEPFILTERS ( customer[customer] IN _toplist ) )

Re: Top Customers

Oros — Mon, 17 Jun 2024 16:07:35 GMT

Hi Jihwan_Kim,

Thank you very much for your quick reply and for taking the time to send a pbix file. I think that you are very close to the intended result.

One thing that I noticed is that the 2 tables do not show the top 3 per month.

(we do NOT need the top 3 by grand total)

(we need to show all the top 3 customers in a given month. we do NOT need show the customers who have never been in the top 3 in a given month. the ones highlighted in red have never been in the top 3 in any of the given months).

All customers in yellow have been in the top 3 in a given month. Thanks again.

Re: Top Customers

Jihwan_Kim — Mon, 17 Jun 2024 16:58:09 GMT

Hi,

Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.

Sales top 3 v2: = CALCULATE ( SUM ( sales[sales] ), KEEPFILTERS ( WINDOW ( 1, ABS, 3, ABS, CALCULATETABLE ( SUMMARIZE ( sales, customer[customer], 'calendar'[MOnth-Year sort], 'calendar'[Month-Year] ), REMOVEFILTERS ( customer[customer] ) ), ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), DESC ) ) ) )

Re: Top Customers

Oros — Tue, 18 Jun 2024 00:37:50 GMT

Hi Jihwan_Kim,

I think that you actually nailed it! How do I show the rest of the numbers like this? I would like to show the non-highlighted numbers, together with the yellow highlighted numbers. Thanks again.

Re: Top Customers

Jihwan_Kim — Tue, 18 Jun 2024 03:30:03 GMT

Hi,

Please check the below picture and the attached pbix file.

Sales top 3 v3: = VAR _t = ADDCOLUMNS ( SUMMARIZE ( ALLSELECTED ( sales ), customer[customer], 'calendar'[MOnth-Year sort], 'calendar'[Month-Year] ), "@result", CALCULATE ( SUM ( sales[sales] ), KEEPFILTERS ( WINDOW ( 1, ABS, 3, ABS, CALCULATETABLE ( SUMMARIZE ( sales, customer[customer], 'calendar'[MOnth-Year sort], 'calendar'[Month-Year] ), REMOVEFILTERS ( customer[customer] ) ), ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), DESC ) ) ) ) ) VAR _list = SUMMARIZE ( FILTER ( _t, [@result] <> BLANK () ), customer[customer] ) RETURN CALCULATE ( SUM ( sales[sales] ), KEEPFILTERS ( customer[customer] IN _list ) )

Re: Top Customers

Oros — Tue, 18 Jun 2024 15:05:04 GMT

Hi Jihwan_Kim ,

You are an absolute Superhero!!! It works! Thanks!