CODEWITHSUNDEEP
powerpivotdax
Kyle Horseman
Kyle Horseman

Reputation: 45

Get the most common item in a calculated column

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

  1. 32911 |Joe Bob | Engineering | Miami | Florida
  2. 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?

Upvotes: 2

Views: 3706

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

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.

Upvotes: 6

Related Questions