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

[oscarr29]

Hi all

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

pls refer to Pic

Thanks

[OwenAuger · Accepted solution]

Hi @oscarr29

I've uploaded a pbix file here.

I just created a table matching your example, just called it Data

 Create [detail concatenated] measure

 Drag elements into a table

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

Regards,

Owen

[OwenAuger]

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

[oscarr29]

Hi @OwenAuger

thanks for replay.

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

Thanks

[OwenAuger · Accepted solution]

Hi @oscarr29

I've uploaded a pbix file here.

I just created a table matching your example, just called it Data

 Create [detail concatenated] measure

 Drag elements into a table

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

Regards,

Owen

[oscarr29]

Hi @OwenAuger

If at Queries there, how to make it?

[v-shex-msft]

Hi @oscarr29,

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

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

[oscarr29]

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

[v-shex-msft · Accepted solution]

Hi @oscarr29,

Below are some detail operation steps and comment.

First, group data by "Group by" function.

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}})

Reference link:

Power Query Operators

Text.Combine

Regards,

Xiaoxin Sheng

[oscarr29]

Hi @v-shex-msft

thanks for your help and detail operation steps..