Showing Top 3 Text Values tied to TOPN 3 values

[novotnajk]

Hi!

I've created a formula to show me the total headcount found in the top three regions (CBSAs), which produces a value of 2.11M in my model.  

(A) # Top 3 CBSA Members =

VAR

RankingCBSAMembers = VALUES('Member'[CBSA_Abb])

RETURN

CALCULATE([# Member Month Count],

TOPN( 3,

ALL('Member'[CBSA_Abb]),

[# Member Month Count]),

RankingCBSAMembers)

Now, I want to be able to create a string text that shows the names of the top 3 CBSAs.

Essentially, I'm trying to create the following:

Top CBSA Names = "The top 3 CBSAs represent " & FORMAT([(A) # Top 3 CBSA Members,"#,#") & " which consist of the following CBSAs: " & (NEED A MEASURE TO CALCULATE TOP 3 NAMES TIED TO THE (A) # Top 3 CBSA Members)

The top 3 CBSAs are National (1,052,218), Los Angeles Metro Area (793,053) and Chicago Metro Area (269,028).

Any help would be appreciated!

[PaulDBrown]

@novotnajk 

The TOPN function returns a single (scalar and numerical) value (it's confusing, I know).

To do what you need you should use RANKX. If you need to return a scalar text value (a string of values/names) based on a filter on the rank value, you can subsequently use CONCATENATEX(FILTER....).

Happy to expand if you get stuck (but please provide either a PBIX file or a sample dataset)!

[novotnajk]

Would you be able to show me the formula you would use? I'm trying to avoid any slicers or separate filters so as to show "predictive text"

[PaulDBrown]

@novotnajk 

Sure, no problem...but it would be very helpful if you provided either a sample (non confidential) dataset or PBIX file itself. The way your model is set up is of paramount importance (it always is...)

otherwise we are just guessing when writing measures...

[PBIDevFW]

Ugh. Simple enough question. You don't need a sample to do it. 

[novotnajk]

Here's the table that I'm using:

CBSA                                            # Member Month Count
National                                       1052218
Los Angeles Metro Area                793053
Chicago Metro Area                      269028
Greater Inland Empire Area           108374
Denver Metro Area                        107896
Dallas Metro Area                            69337
Philadelphia Metro Area                  66654
Greater Phoenix Area                       63122
Atlanta Metro Area                          59860

[PaulDBrown]

@novotnajk 

So which is the column for members for the  headcount?

[novotnajk]

# Member Month Count contains the values (essentially people who live in one of the CBSAs or regions).

For example, the CBSA "National" has 1,052,218 members living in that region.

[PaulDBrown]

Ok, thanks for that.

So... what you need is the top 3 based on the table you posted as an example? (Ie by location?) what is the table & column which defines the filter context for the location in the table you posted? is the headcount aggregation a measure? If so, what is the measure?

BTW, having "National" in your table. Does that mean you wish to include "National" in your top 3?

[novotnajk]

I've already created the measure to calculate the total members of the top 3 CBSAs.  That is the formula I referenced above.  What I need to do now is to create a string text that basically identifies those three CBSAS.  For example, 

Top 3 Text CBSAs = If(CBSA IN [Top 3 CBSA Members], [Top3 CBSA Names].

Therefore the [Top 3 CBSA Names] would show, "National", "Los Angeles Metro Area", "Chicago Metro Area"

[PaulDBrown]

@novotnajk 

Yes, I know. The problem is you need to establish the CBAS which are in the top 3. That's what I'm trying to help you with. We need to use RANKX.

To do so so we need to rank the CBAS (whatever they are) based on the filter context you need (by Total locations, by location...etc...). Once we have the rank, we can then list them in a measure with CONCATENANTEX

[novotnajk]

So, this formula shouldn't be used, correct?

(A) # Top 3 CBSA Members =

VAR

RankingCBSAMembers = VALUES('Member'[CBSA_Abb])

RETURN

CALCULATE([# Member Month Count],

TOPN( 3,

ALL('Member'[CBSA_Abb]),

[# Member Month Count]),

RankingCBSAMembers)

[novotnajk]

I tried the formula below, but it doesn't work either:

(A) # Rank CBSAs =

RANKX ( ALL ('Member'[CBSA_Abb]), CALCULATE(DISTINCTCOUNT('Member'[MemberID])))

[novotnajk]

Could you show me the formula you'd use? I'm trying to avoid segmentations or separate filters to show "predictive text"