Showing top N customers in selected customer's region

[Anaxielia]

Hi, 

This is a part of two questions, original post is here: link 

I have a table (named "data") that records all sales, example is shown below, (there are several columns but i ignored that are unrelated to this topic):

Customer Name	Amount	Sale date	customer region
customer 1	$1000	01.02.2024	West
customer 1	$2000	01.03.2024	West
customer 1	$1000	01.04.2024	West
customer 2	$1000	01.05.2024	West
customer 2	$1000	01.06.2024	West
customer 2	$1000	01.07.2024	West
customer 3	$1000	01.08.2024	West
customer 3	$1000	01.09.2024	West
customer 4	$1000	01.02.2024	West
customer 5	$1000	01.09.2024	East
customer 5	$1000	01.06.2024	East
customer 6	$1000	01.05.2024	East
customer 6	$1000	01.06.2024	East
customer 6	$1000	01.03.2024	East
customer 7	$1000	01.02.2024	East
customer 7	$1000	01.02.2024	East
customer 8	$1000	01.01.2024	East
customer 8	$1000	01.08.2024	East
customer 8	$1000	01.08.2024	East
customer 9	$1000	01.06.2024	East
customer 9	$1000	01.07.2024	East

In addition, i have a slicer that filters Customer Name. 

I try to make a table (or matrix table) that shows top N (let's say 3) customers and total sales amount in selected customer's region.

For example, when i select "customer 7" in slicer, i want to see  "Top 3 Customers and Sales amounts in East Region" at the table.

I tried several things,

1) I used a measure to rank customers in each region. which works fine. I put this measure in "visual filters"and select "less than 3". however table (or matrix table) shows both regions' top 3 customers. not selected customer's region.

2) i tried to disconnect the table from slicer as interactions, however as you guess, this also had no use.

3) i created a measure as can be seen in original post, however this is also not worked.

4) I have a hard time to understand within the RANKX formula. I tried myself to modify some codes but couldnt find a way.

Thanks for help or suggesting other posts. Both of solutions are appreciated. 

Thanks. 

Edit: some spelling errors. 

*original data have 300K rows, 8 regions and 92 customers

[ThxAlot]

[Anaxielia · Accepted solution]

Hi, thanks for suggestion. 

I looked your pbix and yes, that is what i am looking for. 

I mimic everything in your post and pbi however something is not right. 

What i do: 

1) Created a table for customer names and made an one to many relationship as you do.

2) created a measure as you coded and tried to make a parameter as in your pbi.

What happened: when i select a customer in slicer, tablo shows no value if selected customer is in that top N which means that is not what i expected. 

If i disconnect the table from slider, table show the top N (slider value) customers in each region. 

I definitely doing sth wrong but my brain is toasted rn. 

EDIT: I think there is problem at Customer Name selection. Because, in your example file, when i choose no the customers (all customers in other words) in Customer Name slicer of yours, "Top N in the Region" table shows exactly the same thing with mine, Top N customers from each region. But in my file, whenever i choose a customer, it only shows that customer IF it is in the Top N of its region.

EDIT#2: I think i solved some of it. I changed the slicer's data (from Customer Name column in "data" table to "d_Customer table's Customer Name column). Even thought it worked for some of customers, there is a problem. When i select a customer, table shows unrelated region's customers, or, not showing all the customers in that region. 

I looked deep through and saw that selected customer's region infor is not true and have multiple Regions for that customer. 

Relationship between region and customer name is faulty i guess but i cant figure out where. (Note, in original data, there is no multiple region entry for any customers.)

EDIT #3: I use unrelated  table's "Customer Name" column, instead of the one i said :'). So it worked now but "Rank in region" info card is broken. It shows "1" to all customers. I looked and same problem occurs in example pbix, too. 

EDIT #4: I found the solution. I renewed the measure with all columns in d_Customer table's values and it is working now. I am writing this for anyone who wants to looking for help to same problem. Thank you all. 

[danextian]

Hi @Anaxielia 

Create this measure:

Customer Rank by Amount =
RANKX (
    // Remove all filters on 'Customer Name' to ensure ranking is done across all customers
    ALL ( 'Table'[Customer Name] ),
    // The expression to rank: [Sum of Amount] (assumed to be a measure that calculates total sales/amount per customer)
    [Sum of Amount],
    ,
    // Rank in descending order (higher amounts get a lower rank number)
    DESC,
    // Use DENSE ranking (if two customers have the same amount, they receive the same rank, and the next rank is the next sequential number)
    DENSE
)

Create a numeric range parameter table

Create a measure that returns just the amount withink the selected TopN/rank

TopN Sales = 
CALCULATE (
    [Sum of Amount],
    FILTER (
        VALUES ( 'Table'[Customer Name] ),
        [Customer Rank by Amount] <= [Rank Value]
    )
)

Please see the attached sample pbix.

[Anaxielia]

I am sorry but no success. With your suggestion, i am able to have dynamic filter for Top N for all sales. However, i am struggling with a different problem. i need to see Top 3 (or 5, which is not imprtant) customers of the same Region for the customer that i select in filter. 

[Deku]

Please see this SQLBI video, just ignore the parts for including a "other" row

[Anaxielia]

I used this solution for a different problem, great video. 

However that doesn't solve mine, if i repeated all the steps correctly. With this video, i am able to place a dynamic Top N filter with rest shown as "others", and in this video, slicer also only for the main product group. You select the Main product group (or brand) and rest is followed. 

If i understand correctly, this video shows me if i had Region filter and want to see top N customers in table. Alas, that is not exact thing that i need. 

to be clear, i have a customer filter. When i choose a customer, i want this table that show me Top N customers and sales amount in same region with it. for Example, when i select Customer 7 in filter, i want to see Top N customers in East Region in this visual without needing another filter/slicer for Region.