cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Tasos Regular Visitor
Regular Visitor

Summarising table based on multiple values

 Hello,

 

I am looking for something that can be either quite easy or extremely difficult.

 

I have a table like the following one where different lines may have the same ID and same or different values in a second column. I need to create a new table (by summarising the original one) like this:

  • 100000019 "Home & Complementary"
  • 100000020 "Home" 
  • 100000021 "Underwear"
  • 100000028 "Womenswear"
  • etc

 

Capture.JPG

 

I hope that my question is quite clear.

 

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
MarcoRotta Regular Visitor
Regular Visitor

Re: Summarising table based on multiple values

Hi there.

 

In the example below I'm using your original data table layout (as in your picture) but named as "Data" in my model:

 

sample_table.PNG

 

 

 

 

 

 

 

 

 

To create a summarized table using DAX, just go to "Modeling > New Table" and type:

 

New Table = values(Data[orders])

 

This creates a new table with the unique values of orders. You can change "New Table" above to whatever name you like for the table, of course.

 

Now you can go to "Modeling > New Column" and add a DAX expression to concatenate the different values in different rows for each order, which is:

 

ConcatenatedValues = CONCATENATEX(FILTER(Data,Data[orders]='New Table'[orders]),Data[department]," | ")

 

In the expression above, the last parameter is the separator you want. I've used "|", but it could be you "&" or anything else.  

 

Final result:

 

final_table.PNG

 

 

 

 

 

 

 

 

Hope it helps.

3 REPLIES 3
danextian New Contributor
New Contributor

Re: Summarising table based on multiple values

Hi @Tasos

 

Correct me If I am wrong but it appears to me that you simply want all Home and Complementary products columns to be treated as one. If so, you may try creating a calculated column in DAX using this formula

 

 

Department2 =
IF (
    'TableName'[Department] = "Home"
        || 'TableName'[Department] = "Complementary products",
    "Home and Complementary",
    'TableName'[Department]
)

 

 

Then use this newly calculated column in the table visual instead of the old one.

 

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
MarcoRotta Regular Visitor
Regular Visitor

Re: Summarising table based on multiple values

Hi there.

 

In the example below I'm using your original data table layout (as in your picture) but named as "Data" in my model:

 

sample_table.PNG

 

 

 

 

 

 

 

 

 

To create a summarized table using DAX, just go to "Modeling > New Table" and type:

 

New Table = values(Data[orders])

 

This creates a new table with the unique values of orders. You can change "New Table" above to whatever name you like for the table, of course.

 

Now you can go to "Modeling > New Column" and add a DAX expression to concatenate the different values in different rows for each order, which is:

 

ConcatenatedValues = CONCATENATEX(FILTER(Data,Data[orders]='New Table'[orders]),Data[department]," | ")

 

In the expression above, the last parameter is the separator you want. I've used "|", but it could be you "&" or anything else.  

 

Final result:

 

final_table.PNG

 

 

 

 

 

 

 

 

Hope it helps.

Tasos Regular Visitor
Regular Visitor

Re: Summarising table based on multiple values

Hello both,

 

Thank you for the replies and apologies for my late reply.

 

@danextian, what you proposed could work, however, I have had multiple combinations and therefore your approach wasn't easy to be applied. What @MarcoRotta had proposed worked for me.

 

Once again, thank you for your time and the support.