Rolling up a list via the most common value in a column

[michaeljhenley0]

Hello,

I am relatively new to Power BI. I'm trying to create a list that surveys a large table full of contact, geographic and policy info.

I have two tables. Table 1 starts with a text string of State-County-Policy # and then invidual locations with individual contact names and numbers. Often, there are repeats.

State/County/Policy	Location	Contact Name	Contact Number
AK-ANCHORAGE-0001	1	GUY SMITH	9999999999
AK-ANCHORAGE-0001	2	GUY SMITH	9999999999
AK-ANCHORAGE-0001	3	GUY SMITH	9999999999
AK-ANCHORAGE-0001	4	DAVID SMITH	8888888888
AK-ANCHORAGE-0001	5	DAVID SMITH	8888888888
AK-ANCHORAGE-0001	6	DAVID SMITH	8888888888
AK-ANCHORAGE-0002	7	PETER SMITH	7777777777
AK-ANCHORAGE-0002	8	PETER SMITH	7777777777
AK-ANCHORAGE-0002	9	PHIL SMITH	6666666666
AK-ANCHORAGE-0003	10	JOHN SMITH	5555555555

Table 2 is a summarized version of this first table where I want to roll up table 1 into one State-County-Policy instance per line, with the contact name and contact number populated by the most common value that occured in each of those columns for that state-county-policy combination.

State/County/Policy	Contact Name	Contact Number
AK-ANCHORAGE-0001
AK-ANCHORAGE-0002
AK-ANCHORAGE-0003

So, in other words I would want table 2 to look like this, selecting the most common values from filtering table 1 via the state/county/policy.

State/County/Policy	Contact Name	Contact Number
AK-ANCHORAGE-0001	GUY SMITH	9999999999
AK-ANCHORAGE-0002	PETER SMITH	7777777777
AK-ANCHORAGE-0003	JOHN SMITH	5555555555

Is this possible?

[v-yiruan-msft · Accepted solution]

Hi @michaeljhenley0 ，

You can create a calculated table as below:

Table 2 = 
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[State/County/Policy],
        'Table'[Contact Name],
        'Table'[Contact Number],
        "countofcn", CALCULATE ( COUNT ( 'Table'[Contact Number] ) )
    )
VAR _table2 =
    ADDCOLUMNS (
        _table,
        "maxcount",
            MAXX (
                FILTER ( _table, [State/County/Policy] = EARLIER ( [State/County/Policy] ) ),
                [countofcn]
            )
    )
VAR _table3 =
    SUMMARIZE (
        FILTER ( _table2, [countofcn] = [maxcount] ),
        [State/County/Policy],
        [Contact Name],
        [Contact Number]
    )
RETURN
    _table3

Best Regards