Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

@Anonymous

 

You can use this MEASURE

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

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

 

 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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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.

@Anonymous

 

You can use this MEASURE

 

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

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors