DAX: Identify TOP city per customer based on sales value

[irenelitw629]

Hi,

I have customer, city, sales data in 3 separate tables - relationship as per attached file.

I want to write a DAX that can identify the Top city per customer based on the sales amount.

Can someone help me? I wrote a DAX to rank the city but not able to write another DAX to identify top city.

My DAX measure for Sales Rank=

RANKX(ALL(city), CALCULATE(sum(sales), ALLEXCEPT('customer', 'customer name)))

Example:

Customer Name	City	Sales	Top City
A	Singapore	$10,250,257	Singapore
B	Paris	$4,335,949	Paris
B	Tokyo	$3,691,496	Paris
B	New York	$ 705,351	Paris

Thanks for your help.

Pbix: https://drive.google.com/file/d/15mNrBRl809vWkiiUxnox35Umb6XinouE/view?usp=drive_link

[Anonymous]

Hi, @irenelitw629 

You cam refer to @amitchandak reply. If it doesn't meet your needs, will you be able to re-share your PBIX file (Due to the Privacy Policy, please set do not require an account login to download the file.)

Best Regards,
Yang
Community Support Team

[amitchandak]

@irenelitw629 , Try index function

calculate(sum(sales[Sales]), keepfilters(index(1, Summarize(allselected(sales), City[City], Customer[Customer Name]), orderBy(City[City], desc) ,partitionBy(Customer[Customer Name]))))

Continue to explore Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U