Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Progressive
Regular Visitor

Combine and SUM multiple columns

Hi,

 

I created dashbord table and I would like to combine the column1 with commas and SUM the column2 based on column3. Is it possible?

 

I have a table like this,

Column1,Column2,Column3

A5XX
B10XX
C12XX
D3YY
F8YY
G15ZZ
Y4SS
H1BB

 

Final result should be like below

Column1,Column2,Column3

A,B,C27 (5+10+12)XX
D,F11(3+8)YY
G15ZZ
Y4SS
H1BB

 

Thanks in advance.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Create a meausre like this:

 

Measure = CONCATENATEX(VALUES('Table'[Column1]),[Column1],",") 

Create a table visualization with Column 3, Column 2 with default SUM and this measure.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
v-yulgu-msft
Employee
Employee

Hi @Progressive,

 

Alternatively, you could new a calculated table with this formula:

New Table =
SUMMARIZE (
    Table,
    Table[Column3],
    "Column1", CONCATENATEX ( Table, Table[Column1], "," ),
    "Column2", SUM ( Table[Column2] )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yulgu-msft,

I tried your formula and it works with one downside. Now, I have duplicates in Column1. Like A,B,C,A,A,A,A

Is there anyway to remove dubs in Column1. Something like DISTINCT SUMMARIZE 🙂

 

 

Greg_Deckler
Super User
Super User

Create a meausre like this:

 

Measure = CONCATENATEX(VALUES('Table'[Column1]),[Column1],",") 

Create a table visualization with Column 3, Column 2 with default SUM and this measure.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.