Forum Discussion

bvilten's avatar
bvilten
Helper III
7 years ago
Solved

Combine rows based on column value

I am not sure exactly how to phrase this, which probably explains why I can't find a pre-existing answer. Anyway I have a table that has multiple items which are repeated throughout by month and yea...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi bvilten,

     

    You can try to use following formulas to extract item group and combine sub items.

     

    Calculate column formulas:

    Item Group = PATHITEM(SUBSTITUTE([Item],"/","|"),1)

Combined = 
CALCULATE (
    CONCATENATEX (
        VALUES ( Sheet1[Item] ),
        REPLACE (
            [Item],
            1,
            LEN ( PATHITEM ( SUBSTITUTE ( [Item], "/", "|" ), 1 ) ) + 1,
            ""
        ),
        "/"
    ),
    FILTER ( ALL ( Sheet1 ), [Item Group] = EARLIER ( Sheet1[Item Group] ) )
)

     

    Regards,

    Xiaoxin Sheng

bvilten's avatar
bvilten
Helper III
7 years ago

Hello All,

Turns out I was making this way too complicated. So if you are looking for a non coded solution to aggregating rows based on a column value  try this. In either the Data or Report view click on ... to the right of column name you want to merge and select group. The next window will show you a listing of all unique entries in that column, CRTL click the entries you want combined then click group and name the group. Drop the group you created into a visualization and there you have it.

 

 

  • jvanarsdale's avatar
    jvanarsdale
    Frequent Visitor
    5 years ago

    This worked great for my visual! Thank you!