Get the most common item in a calculated column

Question

I've figured out how to accomplish the equivalent of the following in a measure, but I need to use it as the legend in a Power View chart so it needs to be done in a calculated column. The change of context from calculated field to calculated column has completely screwed me up.

In my data model, I have a table of job applications. Each record has a single Specialty and an address for the company being applied to. Each specialty can show up multiple times in the table.

ApplicationTable:

ApplicationID | Name | Specialty | City | State

32911 |Joe Bob | Engineering | Miami | Florida

89547 |Ralph Kramden | Shouting | New York | New York

etc.

I also have a table of states. It just has columns for state name and postal abbreviation. I need to create a column with the most commonly occurring Specialty per state.

If I could do this as a calculated field, I would have been finished hours ago. I just used a pretty straightforward application of topn:

Top Specialty := FIRSTNONBLANK (TOPN (3, VALUES (ApplicationTable[Specialty]),[Count of ApplicationID], ApplicationTable[Specialty])

I used FIRSTNONBLANK and TOPN(3...) because some states only have a few applications, so each specialty only shows up once. In my application it's fine to just pick the first specialty in the list in those cases.

Anyway that formula is cool but it doesn't help here. So how do I do the equivalent in a calculated column, so I can use it as a key or a filter? Specifically, I think I need to do this in the StateTable, giving me the name of the specialty that occurs most per state in the ApplicationTable. Ideas?

Kyle Hale · Accepted Answer · 2015-08-07 14:34:06Z

6

First, create a basic measure to count specialties:

SpecialtyCount :=
COUNTA ( ApplicationTable[Specialty] )

Next, create a measure to figure out the highest single specialty (within a context):

MostSpecial :=
MAXX ( VALUES ( ApplicationTable[Specialty] ), [SpecialtyCount] )

Finally, add a calculated column to your States table:

=
FIRSTNONBLANK (
    ApplicationTable[Specialty],
    IF (
        [SpecialtyCount]
            = CALCULATE ( [MostSpecial], VALUES ( ApplicationTable[Speciality] ) ),
        1,
        BLANK ()
    )
)

By placing this as a calculated column, our filter context is each state. So first PowerPivot will filter the ApplicationTable to just applications within the state, and then it will use FIRSTNONBLANK() to iterate through each ApplicationTable[Specialty], calculate its SpecialtyCount and see if that equals the MostSpecial count within that state. If so, it's not blank, and that's the specialty it returns.

edited Aug 7, 2015 at 14:34

answered Aug 5, 2015 at 20:42

Kyle Hale

8,1301 gold badge40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kyle Horseman Over a year ago

That's exactly what I was looking for. I don't think it ever would have occurred to me to use that calculate values combo to retrieve the condition criteria. This gives me quite a few new things to think about. Nicely done.

Collectives™ on Stack Overflow

Get the most common item in a calculated column

ApplicationID | Name | Specialty | City | State

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

ApplicationID | Name | Specialty | City | State

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related