Top N Customers Compared to Last Year's Top N

[dgefcoe]

I have sales data for several customers over time. I am looking to compare this year's top N customers (5 in the example) with last year's top N customer. I am NOT comparing this year's Top N customers against themselves in the prior year.

My sample data below:

Year	Customer	Sales
2023	A	173
2023	B	212
2023	C	278
2023	D	588
2023	E	104
2023	F	921
2023	G	721
2023	H	775
2023	I	569
2023	J	891
2024	A	310
2024	B	443
2024	C	64
2024	D	833
2024	E	58
2024	F	465
2024	G	828
2024	H	12
2024	I	203
2024	J	675

The table I am trying to build looks like this:

Customer Rank	2023	2024	2023 Sales	2024 Sales	% Change Sales
1	F	D	921	833	-10%
2	J	G	891	828	-7%
3	H	J	775	675	-13%
4	G	F	721	465	-36%
5	D	B	588	443	-25%

In 2023 my top customer was F with sales of 921. In 2024 my top customer was D with sales of 833. This was a 10% decrease in sales to my top customer.

[TomMartens]

Hey @dgefcoe ,

Please comment on my solution if it does not return what you are expecting, if tackles your challenge mark my solution as answer, this will help other users of this community.

Thanks,

Tom

[TomMartens · Accepted solution]

Hey @dgefcoe ,

from the sample data I created this model:

the Table Ranking is based on the below DAX statement:

Ranking = 
SELECTCOLUMNS(
    GENERATESERIES( 1 , 10 , 1 ),
    "Rank", ''[Value]
)

The above DAX creates a simple table of integers that will be used as a rank.

With some measures I'm able to create the following table visual:

The measures are:

this years Customer = 
var topnCustomer = 5
var currentRank = SELECTEDVALUE( 'Ranking'[Rank] )
var currentYear = SELECTEDVALUE( 'dim Years'[Year] )
var thisYearsTopNCustomerPath = 
    CONCATENATEX(
        TOPN( 
            topnCustomer,
            CALCULATETABLE(
                'fact Sales',
                'dim Years'[Year] = currentYear
            ),
            CALCULATE( SUM( 'fact Sales'[Sales] ) ),
            DESC
        ),
        'fact Sales'[Customer],
        "|",
        CALCULATE( SUM( 'fact Sales'[Sales] ) ),
        DESC
    )
var Result = PATHITEM( thisYearsTopNCustomerPath , currentRank)
return
Result

this years sales = 
var topnCustomer = 5
var currentRank = SELECTEDVALUE( 'Ranking'[Rank] )
var currentYear = SELECTEDVALUE( 'dim Years'[Year] )
var thisYearsTopNCustomerPath = 
    CONCATENATEX(
        TOPN( 
            topnCustomer,
            CALCULATETABLE(
                'fact Sales',
                'dim Years'[Year] = currentYear
            ),
            CALCULATE( SUM( 'fact Sales'[Sales] ) ),
            DESC
        ),
         CALCULATE( SUM( 'fact Sales'[Sales] ) ),
        "|",
        CALCULATE( SUM( 'fact Sales'[Sales] ) ),
        DESC
    )
var Result = PATHITEM( thisYearsTopNCustomerPath , currentRank)
return
Result

prev years Customer = 
var topnCustomer = 5
var currentRank = SELECTEDVALUE( 'Ranking'[Rank] )
var prevYear = SELECTEDVALUE( 'dim Years'[Year] ) - 1
var prevYearsTopNCustomerPath = 
    CONCATENATEX(
        TOPN( 
            topnCustomer,
            CALCULATETABLE(
                'fact Sales',
                'dim Years'[Year] = prevYear
            ),
            CALCULATE( SUM( 'fact Sales'[Sales] ) ),
            DESC
        ),
        'fact Sales'[Customer],
        "|",
        CALCULATE( SUM( 'fact Sales'[Sales] ) ),
        DESC
    )
var Result = PATHITEM( prevYearsTopNCustomerPath , currentRank)
return
Result

prev years Sales = 
var topnCustomer = 5
var currentRank = SELECTEDVALUE( 'Ranking'[Rank] )
var prevYear = SELECTEDVALUE( 'dim Years'[Year] ) - 1
var prevYearsTopNCustomerPath = 
    CONCATENATEX(
        TOPN( 
            topnCustomer,
            CALCULATETABLE(
                'fact Sales',
                'dim Years'[Year] = prevYear
            ),
            CALCULATE( SUM( 'fact Sales'[Sales] ) ),
            DESC
        ),
        CALCULATE( SUM( 'fact Sales'[Sales] ) ),
        "|",
        CALCULATE( SUM( 'fact Sales'[Sales] ) ),
        DESC
    )
var Result = PATHITEM( prevYearsTopNCustomerPath , currentRank)
return
Result

and change, you have to adjust it so that it will become 10 ...

change = 
DIVIDE( [this years sales] , [prev years Sales] )

Hopefully, this provides what you are looking for.

Regards,

Tom

[TomMartens]

Hey @dgefcoe , 

there is no Customer v in your sample data, is this on purpose?

Regards,

Tom

[dgefcoe]

You are correct, that should read D. I will fix it.