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

how to use power bi Group same name with different detail and sum total

Hi all

anyone know how to use power bi Group same name with different detail and sum total?

pls refer to Pic

Thanks

Example.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: how to use power bi Group same name with different detail and sum total

Hi @oscarr29

 

I've uploaded a pbix file here.

 

I just created a table matching your example, just called it Dataimage.png

 Create [detail concatenated] measure

image.png

 Drag elements into a tableimage.png

 

You may want to suppress the totals for [detail concatenated] - would require a tweak.

 

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Community Support Team
Community Support Team

Re: how to use power bi Group same name with different detail and sum total

Hi @oscarr29,

 

Below are some detail operation steps and comment.

 

First, group data by "Group by" function.

5.gif

 

Comment:

Column 'Detail', 'Deposit' and 'Sell' need to be summarized. For 'Deposit' and 'Sell' can simply use existed 'sum' option to get summary value.

Current power query not contains exists function to directly transform 'detail' column, so I store all group records data as contents. (we need to modify function formula to convert data)

 

Since you only need to expand detail column items to one cell, so I use ['Column Name'] to get grouped 'Detail' column item list.

= Table.Group(#"Changed Type", {"Name"}, {{"Detail", each _[Detail], type table}, {"Deposit", each List.Sum([Deposit]), type number}, {"Sell", each List.Sum([Sell]), type number}})

Then, we need to consider to combine items to one, I found power query contains 'Text.Combine' function can used to combine list items to one with specific separator.


I modify query to use 'Text.Combine' to return combined result.

= Table.Group(#"Changed Type", {"Name"}, {{"Detail", each Text.Combine(_[Detail],"*"), type table}, {"Deposit", each List.Sum([Deposit]), type number}, {"Sell", each List.Sum([Sell]), type number}})

6.gif

 

Reference link:

Power Query Operators

Text.Combine

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

View solution in original post

8 REPLIES 8
OwenAuger Super Contributor
Super Contributor

Re: how to use power bi Group same name with different detail and sum total

Hi @oscarr29

 

If you just want to create a table visual with conatenated detail column (leaving underlying data unchanged), you can create a measure like this:

 

detail concatenated =
CONCATENATEX ( VALUES ( YourTable[detail] ), YourTable[detail], "*" )

Then add it as a value field in a table along with Name/deposit/sell.

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




oscarr29 Regular Visitor
Regular Visitor

Re: how to use power bi Group same name with different detail and sum total

Hi @OwenAuger

thanks for replay.

can you pls make a sample pic, how to create ?

Thanks

OwenAuger Super Contributor
Super Contributor

Re: how to use power bi Group same name with different detail and sum total

Hi @oscarr29

 

I've uploaded a pbix file here.

 

I just created a table matching your example, just called it Dataimage.png

 Create [detail concatenated] measure

image.png

 Drag elements into a tableimage.png

 

You may want to suppress the totals for [detail concatenated] - would require a tweak.

 

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

oscarr29 Regular Visitor
Regular Visitor

Re: how to use power bi Group same name with different detail and sum total

Hi @OwenAuger

 

If at Queries there, how to make it?

 

Q.png

Community Support Team
Community Support Team

Re: how to use power bi Group same name with different detail and sum total

Hi @oscarr29,

 

You can use power query group function to achieve your requirement:

10.PNG

 

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyk7NU9JRSk4sApKGBiCAYMTqwOQz8kuLU4G0EUyFMbqKgoz8vFSoVqhCJNmcxIKS/AKYuI6SKUy6sjQ7E26/qQFCM6o8zH5jqAozDBUw+40Q7gNLl2TmovoP1XyINIrxIBUWUBWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Detail = _t, Deposit = _t, Sell = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Detail", type text}, {"Deposit", Int64.Type}, {"Sell", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Detail", each Text.Combine(_[Detail], "*"), type text},{"Deposit", each List.Sum([Deposit]), type number}, {"Sell", each List.Sum([Sell]), type number}})
in
    #"Grouped Rows"

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
oscarr29 Regular Visitor
Regular Visitor

Re: how to use power bi Group same name with different detail and sum total

Hi @v-shex-msft

you give that solution is solve, but can you screenshot your step to me? because i want to learn how to build it

Thanks

Community Support Team
Community Support Team

Re: how to use power bi Group same name with different detail and sum total

Hi @oscarr29,

 

Below are some detail operation steps and comment.

 

First, group data by "Group by" function.

5.gif

 

Comment:

Column 'Detail', 'Deposit' and 'Sell' need to be summarized. For 'Deposit' and 'Sell' can simply use existed 'sum' option to get summary value.

Current power query not contains exists function to directly transform 'detail' column, so I store all group records data as contents. (we need to modify function formula to convert data)

 

Since you only need to expand detail column items to one cell, so I use ['Column Name'] to get grouped 'Detail' column item list.

= Table.Group(#"Changed Type", {"Name"}, {{"Detail", each _[Detail], type table}, {"Deposit", each List.Sum([Deposit]), type number}, {"Sell", each List.Sum([Sell]), type number}})

Then, we need to consider to combine items to one, I found power query contains 'Text.Combine' function can used to combine list items to one with specific separator.


I modify query to use 'Text.Combine' to return combined result.

= Table.Group(#"Changed Type", {"Name"}, {{"Detail", each Text.Combine(_[Detail],"*"), type table}, {"Deposit", each List.Sum([Deposit]), type number}, {"Sell", each List.Sum([Sell]), type number}})

6.gif

 

Reference link:

Power Query Operators

Text.Combine

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

View solution in original post

oscarr29 Regular Visitor
Regular Visitor

Re: how to use power bi Group same name with different detail and sum total

Hi @v-shex-msft

thanks for your help and detail operation steps..

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 40 members 2,108 guests
Please welcome our newest community members: