Return Rank Value and Show as Label On Map

[Gracie]

Hi,

I have a question now. I have a table with columns including city, longtitude, latitude, product. I want to do a map visual -- Best product by city. The lable on the map will be "city name - its best product name".  Below is the data sample:

My question is how to add a new column to return produt who has the max count for different cities?  Then I can put the column as the location in the map and lable can show the product name in each city.

CITY	LONGTITUDE	LATITUDE	Product
LOS ANGELES	-118.298662	33.786594	BM
LYNWOOD	-118.202954	33.924538	BM
LOS ANGELES	-118.244955	33.921065	MBZ
ENCINO	-118.298662	33.786594	BM
SANTA MONICA	-115.567483	34.202339	BM
SAN BERNARDINO	-116.228393	34.134794	LEX
LONG BEACH	-118.181102	33.831466	CHE
LOS ANGELES	-118.285706	33.938108	FOR
CARSON	-118.261154	33.813317	BM
COMPTON	-118.21681	33.879565	CHE
SANTA MONICA	-115.567483	34.202339	LINC
SANTA MONICA	-115.567483	34.202339	BM
SAN BERNARDINO	-116.228393	34.134794	HON
CORONA	-116.055617	33.752886	INF
LOS ANGELES	-118.298662	33.786594	MBZ
LYNWOOD	-118.202954	33.924538	GMC
SAN BERNARDINO	-116.228393	34.134794	HON
COMPTON	-118.240208	33.874815	LEX
COMPTON	-118.240208	33.874815	CHE
SANTA MONICA	-115.567483	34.202339	BM

Thanks!

[Phil_Seamark · Accepted solution]

Hi @Gracie

You could try adding these three calculated columns to your table.

Count of Products Per City = 
var CityColumn = 'Table1'[CITY]
var ProductColumn = 'Table1'[Product]
var ProductCount = 
    CALCULATE(
        COUNTROWS('Table1'),
        FILTER('Table1',
            'Table1'[CITY] = CityColumn
            && 'Table1'[Product] =ProductColumn
            )
        )
    
return ProductCount       

Top Products = 
var CountColumn = 'Table1'[Count of Products Per City]
var CityColumn = 'Table1'[CITY]
var TopProducts = 
    IF(
    CALCULATE(
        COUNTROWS('Table1'),
        FILTER('Table1',
            'Table1'[CITY]=CityColumn
            && Table1[Count of Products Per City] > CountColumn
            )
            )+0 =0,
            1,
            0)
return TopProducts        

Best Product = 
var CityColumn = ('Table1'[CITY])
var BestProduct = CONCATENATEX(
    FILTER(
            SUMMARIZE(
                FILTER('Table1','Table1'[Top Products]=1),
                'Table1'[CITY],
                Table1[Product]
                ),'Table1'[CITY]=CityColumn)
                ,'Table1'[Product] & " ")
return BestProduct

and then drag the last column to your tooltip.

[Gracie]

Hi @Phil_Seamark

Thanks for your solutions. I tried and it worked mostly. However, there still has some rows that didn't calculate correctly. I don't if it's the problem of formulas or the fields format.