Forum Discussion

Gracie's avatar
Gracie
Icon for Helper II rankHelper II
9 years ago
Solved

Return Rank Value and Show as Label On Map

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.

 

CITYLONGTITUDELATITUDEProduct
LOS ANGELES-118.29866233.786594BM
LYNWOOD-118.20295433.924538BM
LOS ANGELES-118.24495533.921065MBZ
ENCINO-118.29866233.786594BM
SANTA MONICA-115.56748334.202339BM
SAN BERNARDINO-116.22839334.134794LEX
LONG BEACH-118.18110233.831466CHE
LOS ANGELES-118.28570633.938108FOR
CARSON-118.26115433.813317BM
COMPTON-118.2168133.879565CHE
SANTA MONICA-115.56748334.202339LINC
SANTA MONICA-115.56748334.202339BM
SAN BERNARDINO-116.22839334.134794HON
CORONA-116.05561733.752886INF
LOS ANGELES-118.29866233.786594MBZ
LYNWOOD-118.20295433.924538GMC
SAN BERNARDINO-116.22839334.134794HON
COMPTON-118.24020833.874815LEX
COMPTON-118.24020833.874815CHE
SANTA MONICA-115.56748334.202339BM

 

Thanks!

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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.

     

     

     

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      Gracie
      Icon for Helper II rankHelper II
      9 years ago

      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.