TOPN() function in SUMX is not returning the nth level value

[stmicbarr]

I am having an issue with a measure including TopN filter where some of the top records are being exluded.  Example out of Top 10, ranks 9 and 10 are not included in the calculation.

Here is the formula:

Top 10 By Customer =

SUMX(

    TOPN(

        10,

        SUMMARIZE(

            ALLSELECTED('Sales’),

            ‘Sales’[Customer],

            "YTD",[Sales YTD]),

        [YTD],

 DESC

    ),

    [Sales YTD]

)

Here are the results:

Rank	Customer	Sales YTD	Top 10 Customer
1	A	54,666	54,666
2	B	41,265	41,265
3	C	22,659	22,659
4	D	17,864	17,864
5	E	14,689	14,689
6	F	12,464	12,464
7	G	9,894	9,894
8	H	9,893	9,893
9	I	9,800	9,800
10	J	6,148

The calculation is taking place on a values table for sales volume. I have a filter applied from a lookup table so that the sales values are filtered to a specific department.

How can I fix this so that all of the Top 10 are included in the calculation?

[lbendlin]

Please provide sample data that fully covers your issue.  Provide more than 10 rows of sample data.
Please show the expected outcome based on the sample data you provided.

[stmicbarr]

Here is a small sample of the values table I am using for sales data.

Sales (volume)	Customer	Years	Weeks	Department
1615	A	2024	1	Domestic
1360	A	2024	4	Export
255	B	2024	3	Online
1020	B	2024	8	Online
340	C	2024	1	Domestic
1530	C	2024	5	Online
255	C	2024	15	Online
1445	D	2024	20	Export
170	E	2024	8	Domestic
2295	E	2024	25	Domestic
1275	F	2024	2	Online
336	F	2024	7	Online
425	F	2024	15	Export
1445	G	2024	5	Domestic
1275	G	2024	19	Domestic
1530	H	2024	3	Domestic
896	H	2024	14	Domestic
255	H	2024	16	Export
362	H	2024	22	Export
...

The lookup tables on this used in various visuals to provide filtering are simple.  Other values tables that use the lookup tables are those related to Budget volume, etc.

Customer

A

B

C

D

...

Department

Domestic

Import

Online

...

The 'YTD Sales' measure is a calculation that sums the Sales (volume) column for every week prior to the current one.

I need the measure from my originl post - 'Top 10 Customer' - to return the total sum of volume for all of the Customers who would make up the Top 10 by Sales volume.

In the example above, I would expect it to return the total volume of the Top 10 Customers, adjusting the top 10 list accordingly when filtered by Department.

When different department filters are applied, sometimes the measure works as I had intended.  Other times, it will not include the sales volume for the top 10, leaving out the volumes associated with the 9th and 10th place Customers.  

Testing it by increasing the TOPN function to 11 and higher, the 9th and 10th level begins to show up in the summation.

[lbendlin]

You say "Top 10 Customers"  but your sample data has only eight of them. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[stmicbarr · Accepted solution]

Sorry, relatively new to posting. 

In the course of recreating my data model to share here but with the records masked, I found the issue.   I realized that the virtual table I had built should have referenced the Lookup table instead of the Values table.

Thanks for you prompts to share a complete sample data set!  I'll know better in the future.