Forum Discussion

AaronGlenn10's avatar
AaronGlenn10
Icon for Helper III rankHelper III
4 years ago
Solved

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

 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

     

5 Replies

    • AaronGlenn10's avatar
      AaronGlenn10
      Icon for Helper III rankHelper III
      4 years ago

      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?

    • AaronGlenn10's avatar
      AaronGlenn10
      Icon for Helper III rankHelper III
      4 years ago

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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.