cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dsandip59 Member
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
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]
)

View solution in original post

3 REPLIES 3
Highlighted
Super User
Super User

Re: City with Highest Sales but lowest profit

Hi @dsandip59

 

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

 

 CitySalesProfit
A851935
B891575
C932742
D148311
E90001

 

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

 

 CitySalesProfit
A851935
B891575
C932742
D148311
E900012
dsandip59 Member
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
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]
)

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 476 members 3,709 guests
Please welcome our newest community members: