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 - ...
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.

     

     

     

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.