cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tasos
Helper II
Helper II

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

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.

View solution in original post

3 REPLIES 3
danextian
Resident Rockstar
Resident Rockstar

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.

 

Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

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.

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.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!