Forum Discussion

Silverfeet's avatar
Silverfeet
New Member
8 years ago
Solved

Grouping data in a field into multiple bins

Hello, I have Table 1 in Power BI shown below

 

Table 1
GroupNumber of Candidates
A4
B3
C6
BC8
AB12
D5
Total38

 

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

 

Table 2
GroupNumber of Candidates
A16
B23
C14
D5
Total58

 

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.

Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

     

     

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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