Create a new column with filtered data from another column

[Anonymous]

I have a table that has a column with brand names, for example Red1, Red2, Blue1, Blue2, Green1, Green2, Green3, that has corresponding data to each field that is presented in a new column,

I want to take, for example Red1 and Red 2 and combine them into Red and sum the corresponding data.

And I would also want to do the same with Blue1 and Blue2 and not show/remove Green1, Green2 and Green3 from the newly created column.  How do I do that? . If needed, I can try to explain in more details.

[AlB]

Hi @Anonymous

You could create a new column 'Group' in your table with something like this:

Group =
SWITCH (
    1;
    FIND ( "Red"; Table1[Brand]; 1; 0 ); "Red";
    FIND ( "Blue"; Table1[Brand]; 1; 0 ); "Blue";
    FIND ( "Green"; Table1[Brand]; 1; 0 ); "";
    BLANK ()
)

and then you can place a matrix visual on your report with:

1. Table1[Group] in rows and

2.   a measure like  this in 'values' of the matrix:

    MeasureCount=COUNT(Table1[Group]) 

[Anonymous]

@AlBThank you for the help! Is there any way to put all other extra data into one category of that column instead of manually typing Green 1, Green 2 etc. ?

[AlB · Accepted solution]

Hi @Anonymous

Do you mean this?

Group =
SWITCH (
    1;
    FIND ( "Red"; Table1[Brand]; 1; 0 ); "Red";
    FIND ( "Blue"; Table1[Brand]; 1; 0 ); "Blue";
    FIND ( "Green"; Table1[Brand]; 1; 0 ); "";
    "Other category"
)

It will assign "Other category" to anything that is not Red, Blue or Green.