Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Summarizing/grouping together columns

Hi all, have a table of customers, some of which have multiple Customer IDs (like Microsoft in the sample table), but I really would like to classify this as one customer. I'd like to group customers by Customer Name only if the Regions are the same by creating the Modified Customer ID column, which should include a list of unique Customer IDs separated by a comma.

 

So, in this case, Microsoft would be a customer I'd want to group into 1 customer because it has the same region for all Customer IDs, but I would not want to group Amazon as 1 customer because one is in Europe and one is in the US. Please comment if you have any ideas!

 

Customer IDCustomer NameProduct PurchasedRegionModified Customer ID (Desired Column) 
124MicrosoftCloudUS124, 125, 126
124MicrosoftCloudUS124, 125, 126
125MicrosoftData CenterUS124, 125, 126
126MicrosoftData CenterUS124, 125, 126
223AmazonCloudUS223
224AmazonCloudEurope224

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    If I understand correctly, perhaps:

     

     

    Column =
  CONCATENATEX(
    FILTER('Table',[Customer Name] = EARLIER([Customer Name]) && [Region] = EARLIER([Region]))
    [Customer ID],", "
  )

     

  • camargos88's avatar
    camargos88
    6 years ago

    Hi Anonymous ,

     

    Try this one:

    Column =
    VAR _customer = CALCULATETABLE(DISTINCT('Table'[Customer ID]), FILTER('Table', 'Table'[Region] = EARLIER('Table'[Region]) && 'Table'[Customer Name] = EARLIER('Table'[Customer Name])))
    RETURN CONCATENATEX(_customer, 'Table'[Customer ID], ",")
     
  • ChrisMendoza's avatar
    ChrisMendoza
    6 years ago

    Anonymous -

    Measure solution attempt:

    Measure Modified Customer ID = 
IF (
    ISFILTERED ( 'TableName'[Customer Name] ),
    CALCULATE (
        CONCATENATEX (
            DISTINCT ( TableName[Customer ID] ),
            TableName[Customer ID],
            ", "
        ),
        ALLEXCEPT ( 'TableName', 'TableName'[Customer Name], 'TableName'[Region] )
    ),
    BLANK ()
)

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    If I understand correctly, perhaps:

     

     

    Column =
  CONCATENATEX(
    FILTER('Table',[Customer Name] = EARLIER([Customer Name]) && [Region] = EARLIER([Region]))
    [Customer ID],", "
  )

     

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    Try this one:

    Column =
    VAR _customer = CALCULATETABLE(DISTINCT('Table'[Customer ID]), FILTER('Table', 'Table'[Region] = EARLIER('Table'[Region]) && 'Table'[Customer Name] = EARLIER('Table'[Customer Name])))
    RETURN CONCATENATEX(_customer, 'Table'[Customer ID], ",")
     
  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    6 years ago

    Anonymous -

    Measure solution attempt:

    Measure Modified Customer ID = 
IF (
    ISFILTERED ( 'TableName'[Customer Name] ),
    CALCULATE (
        CONCATENATEX (
            DISTINCT ( TableName[Customer ID] ),
            TableName[Customer ID],
            ", "
        ),
        ALLEXCEPT ( 'TableName', 'TableName'[Customer Name], 'TableName'[Region] )
    ),
    BLANK ()
)