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

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

What if Top 10 are each from different cities? 

  • 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?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      Hi,

      Share the download link of your PBI file.

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