Group Rows in a Table based on count

[sbgsmith]

I have a visual showing counts of individuals by County. The data source has multiple rows for each individual and county so it is aggregated using Count Distinct on the County field.

The visual currently looks like this.

COUNTY            Individuals

KINGS	2000
BRONX	1500
NEW YORK	1250
QUEENS	1000
RICHMOND	300
HUDSON	1
ESSEX	1
HENRICO	1

I am looking to group all counties where the value is 1 together and call it "OTHER". I am looking for it to look like this, where Hudson, Essex and Henrico are relabled as "OTHER" and their combined count is shown.

COUNTY            Individuals

KINGS	2000
BRONX	1500
NEW YORK	1250
QUEENS	1000
RICHMOND	300
OTHER	3

The values can change, so I need this to be calculated not just selecting the 3 counties in a group.

[Anonymous · Accepted solution]

Hi  @sbgsmith ,

Here are the steps you can follow：

1. Create calculated Table：.

Table 2 =
var _table1=
SUMMARIZE(
    'Table',
    "COUNTY","Other",
    "Individuals",
    SUM('Table'[Individuals]))
    return
    UNION('Table',_table1)

2. Create calculated column in Table2.

Individuals_Column =
IF(
    'Table 2'[COUNTY]="Other",
    CALCULATE(COUNTROWS('Table 2'),FILTER(ALL('Table 2'),'Table 2'[Individuals]=1))
    ,
    [Individuals])

3. Create calculated Table

Table 3 =
var _table1=
FILTER(
    'Table 2',
    'Table 2'[Individuals_Column]<>1)
return
SUMMARIZE(
    _table1,
    [COUNTY],[Individuals_Column])

4. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly