Try Again: Ranking Regions by Number of People - then Create Measure Showing Names of Top 3 Regions

[novotnajk]

Hi PBI Forum,

Trying to simplify a former post.  Here is what I'm trying to do, but first, the data:

Table Name:  Members

Sample Data Below

MemberID               CBSA_Abb

12345                      National

56789                      National

01234                      National

09876                      Los Angeles Metro Area

54321                      Los Angeles Metro Area

01923                      Chicago Metro Area

Members each have a MemberID that is unique.  I've created a measure to calculate the unique members:

# Member Month Count = CALCULATE(DISTINCTCOUNT([Members[MemberID]))

This measure gives me 6 unique members.

In a table, the data now show the following:

CBSA_Abb                            # Member Mount Count

National                                 3

Los Angeles Metro Area        2

Chicago Metro Area              1

I now want to create a RankX formula that creates a measure called "CBSA Rank".  In essence, the final table will show the following:

CBSA_Abb                              CBSA Rank

National                                 1

Los Angeles Metro Area        2

Chicago Metro Area              3

I'm looking to create this measure first.

==================================================

The second measure I'm looking to create involves text.  What I'd like to be able to do is create a text string measure such as the following:

Display Top 3 CBSA by Members = "The top 3 CBSAs are "& (measure to show National, Los Angeles Metro, Chicago).

Any assistance?  Appreciate the help!

[AllisonKennedy]

This post should help with both those requirements: https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures/?ref=818

Because you want to use a measure for your ranking, you'll need to use RANKX inside a measure, which is not as easy as in a calculated column.

The bottom of this post shows CONCATENATEX, which is how you can build your string. Only difference is you'll need to wrap your table in a FILTER function that filters for Rank <=3 within the CONCATENATEX to get the top 3 results, then use & to combine that with your text:
Measure= "The top three teams are " & CONCATENATEX(FILTER(ALL(Table), [Rank]<=3), Table[CBSA_Abb])

[novotnajk]

Thanks Allison,

Would you be able to suggest a RANKX formula to use?

[AllisonKennedy]

@novotnajk

See attached sample file (below signature) and see the formulas below. Couple of things to note:

• You do not need the CALCULATE in your count of members measure
• I did Top 2 in my sample file because you only provided three CBSAs in your sample data, so wanted to show that it does filter only the top values requested. 

Try these:

Count of Members = DISTINCTCOUNT('Members'[MemberID ])

CBSA Rank = RANKX(DISTINCT(all('Members'[CBSA_Abb])), [Count of Members])

Top 2 = "The top 2 CBSAs are: " & CONCATENATEX(FILTER(ALL('Members'[CBSA_Abb]), [CBSA Rank]<=2),'Members'[CBSA_Abb], ", ")

[novotnajk]

Hi Again Allison,

How can I make the contatenate display the following text:  The top 3 CBSAs by population are: National, Los Angeles, and Chicago.

(Note, National, Los Angeles, and Chicago are in order by population).

I created this formula:

Top 3 CBSAs = 

"The top 3 CBSAs by population are: " & CONCATENATEX(FILTER(ALL('Member'[CBSA_Abb]), [(A) # CBSA Rank]<=3),'Member'[CBSA_Abb],",")

But, what I get is:  

"The top 3 CBSAs by population are: the top 3 CBSAs by population are: ,Los Angeles,Chicago, National

There's a weird comma and the missing word "and" after the 3rd CBSA.  Also, they are not in order by size.

[AllisonKennedy]

Seems like your real data has some BLANKs. See if my blog about it here helps explain why that might be happening. https://excelwithallison.blogspot.com/2020/07/what-does-this-blank-mean.html

To get the 'and' to display you'll need to call them one at a time. So something like:

Top 3 CBSAs =

"The top 3 CBSAs by population are: " & CONCATENATEX(FILTER(ALL('Member'[CBSA_Abb]), [(A) # CBSA Rank]=1),'Member'[CBSA_Abb],",") &
", " & CONCATENATEX(FILTER(ALL('Member'[CBSA_Abb]), [(A) # CBSA Rank]=2),'Member'[CBSA_Abb],",") &
", and " & CONCATENATEX(FILTER(ALL('Member'[CBSA_Abb]), [(A) # CBSA Rank]=3),'Member'[CBSA_Abb],",")

[novotnajk]

Thanks Allison,  yes there is a blank issue, but it's stemming from some missing data in another table that was just fixed.

I created this formula and it works wonderful.  Thanks for your help!

[AllisonKennedy · Accepted solution]

Thanks @novotnajk for the update. Glad the blanks have been fixed and the formula is working. Please can you mark as solution the final formula that works?