Grouping data in a field into multiple bins

[Silverfeet]

Hello, I have Table 1 in Power BI shown below

Table 1
Group	Number of Candidates
A	4
B	3
C	6
BC	8
AB	12
D	5
Total	38

Group and Number of Candidates are Fields. I want to get Table 2 shown below

Table 2
Group	Number of Candidates
A	16
B	23
C	14
D	5
Total	58

So the Group field has been put into bins, for example Group A (16 candidates) in Table 2 consist of all occurrences of A in Table 1 which is A (4 candidates) and AB (12 candidates).

Is there any way this can be done, this would be very useful.

[v-yulgu-msft · Accepted solution]

Hi @Silverfeet,

Two solutions for your reference:

Solution1

You should manually create a Table2 with one single column that lists all unique group values.

Then, add a caculated column in Table2.

Number of Candidates =
CALCULATE (
    SUM ( Table1[Number of Candidates] ),
    FILTER (
        Table1,
        NOT ( ISERROR ( FIND ( EARLIER ( Table2[Group] ), Table1[Group] ) ) ) = TRUE ()
    )
)

Solution2

In Query Editor mode, duplicate Table1 first. Split the [Group] column in duplicated table 'Table1(2)'.

Save above changes. In Data view mode, new a calculated table with below formula:

Table1(3) =
SUMMARIZE (
    FILTER (
        UNION (
            SELECTCOLUMNS (
                'Table1 (2)',
                "Group", 'Table1 (2)'[Group.1],
                "Number", 'Table1 (2)'[Number of Candidates]
            ),
            SELECTCOLUMNS (
                'Table1 (2)',
                "Group", 'Table1 (2)'[Group.2],
                "Number of Candidates", 'Table1 (2)'[Number of Candidates]
            )
        ),
        [Group] <> BLANK ()
    ),
    [Group],
    "Number of Candidates", SUM ( 'Table1 (2)'[Number of Candidates] )
)

Best regards,

Yuliana Gu