Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AaronGlenn10
Helper III
Helper III

Tallying Locations within a City

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

TBT_Location Tally by City 03132022.jpg

 

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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:

vchenwuzmsft_0-1647417315597.png

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.

 

View solution in original post

5 REPLIES 5
v-chenwuz-msft
Community Support
Community Support

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:

vchenwuzmsft_0-1647417315597.png

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
Super User
Super User

Hi,

What if Top 10 are each from different cities? 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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?

Hi,

Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors