cancel
Showing results for
Search instead for
Did you mean:
Member

## City with Highest Sales but lowest profit

Hi,

I am using the below DAX expression

City with Highest Sales but lowest profit = LOOKUPVALUE('Orders'[City],
'Orders'[Profit],
Min('Orders'[Profit]),
'Orders'[Sales],
Max('Orders'[Sales])
)

but did not get anything. Please correct me if I am wrong here.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: City with Highest Sales but lowest profit

@dsandip59

You can use this MEASURE

```Measure =
MINX (
TOPN ( 1, FILTER ( Orders, Orders[Country] = "India" ), [Profit], ASC ),
[City]
)```
3 REPLIES 3
Super User

## Re: City with Highest Sales but lowest profit

I believe Your formula will only fetch a city when a particular city has both Maximum Sales and Minimum Profit out of all Citiies

Otherwise it will return BLANK

So if your data looks like this...your result would be City E

 City Sales Profit A 8519 35 B 8915 75 C 9327 42 D 1483 11 E 9000 1

But with following table the result would be blank as the City with highest sales doesnot have minimum profit

 City Sales Profit A 8519 35 B 8915 75 C 9327 42 D 1483 11 E 9000 12
Member

## Re: City with Highest Sales but lowest profit

HI,

So if I want in India what is city name having lowest profit? and have used below query:

City with lowest profit in India = LOOKUPVALUE('Orders'[City],
'Orders'[Profit],
Min('Orders'[Profit]),
'Orders'[Country],'India'
)

but I am getting error that table name of 'India' cannot found.

Super User

## Re: City with Highest Sales but lowest profit

@dsandip59

You can use this MEASURE

```Measure =
MINX (
TOPN ( 1, FILTER ( Orders, Orders[Country] = "India" ), [Profit], ASC ),
[City]
)```