Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |