City with Highest Sales but lowest profit

[Anonymous]

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.

[Zubair_Muhammad]

Hi @Anonymous

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

[Anonymous]

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.

[Zubair_Muhammad · Accepted solution]

@Anonymous

You can use this MEASURE

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