Tallying Locations within a City

[AaronGlenn10]

Power BI All Stars:

I have a 'Top 100' ranking based on sales figures, and would like to create a summary visual, which displays the number of locations/businesses for each city represented in the ranking -- for example, if 10 of the Top 100 locations are located in Houston, then the visual would return "Houston - 10" [see below].  Your assistance is greatly appreciated!  ~A

[v-chenwuz-msft · Accepted solution]

Hi @AaronGlenn10 ,

Display format: Table visual

In the filter pane, you can choose the Filter Type is Top N. Then set the n of top, in your case is 3, and sort by value is sum(table[values]). Just like the following screenshot show:

create a measure to get the rank.

rank =

var _r = RANKX(ALL('Table'[City]),CALCULATE(SUM('Table'[values]),ALLEXCEPT('Table','Table'[City])),,DESC)

return

IF(isblank(SUM('Table'[values])),BLANK(),_r)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

What if Top 10 are each from different cities? 

[AaronGlenn10]

Or, may be I just list the top five cities - Houston, Dallas, San Antonio, Austin & Fort Worth, and tally the locations for those cities. 

[AaronGlenn10]

I beleive there are 17 cities represented in the Top 100 list ... maybe the best approach is just to list the top 5 cities represented?

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.