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

MFelix's avatar
MFelix
Super User
7 years ago

Hi bvilten,

 

Create the following two measures:

Values total = SUM(Table1[Value])

summary type = CONCATENATEX(ALLSELECTED(Table1[Type]);Table1[Type];"/")

Then add them to your visual.

 

This will give you the result below, using the all selected allows you to add a slicer to select the type you have on your data, because the CONCATENATEX  needs a table so the ALLSELECTED creates that virtual table.

 

 

Regards,

MFelix

bvilten's avatar
bvilten
Helper III
7 years ago

Hi MFelix,

 

Thank you for your response. It is close to what I am looking for, perhaps it is what I am looking for and I am not understanding. The table (Name: CombinedSDPData) below is a better representation of what I have. I already have year and month slicers. So I would want to concatonate the two Active Directory lines, ESS lines, and the two SAP lines as an example. When I create the specified measures I get errors as table1 not found. If I substitute CombinedSDPData for table1 I get items and counts but the counts ignore any filters for year and month. You also mention a virtual table and I am not sure what you mean by that. I am however digging into CONCANTONATE, and CONCANTONATEX functions so thank you for that lead as well

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI bvilten,

     

    You can't create dynamic calculate column/table based on slicer/filter, it can achieve this by measure but measure cannot used as axis fields.

     

    In my opinion, I'd like to suggest you add two calculated columns to your table. One used to store category info of these records, other one used to store concatenate value based on category column.

     

    After these steps, you can simply use 'category' , 'concatenate' columns as row field, 'year' 'month' as column field, 'amount' column as value field to create matrix visual.

     

    If you are confused on coding formula, please share some sample data for test.

     

    Regards,

    Xiaoxin Sheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        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