locked
Aggregating the value of account after combining the multiple row into one. RRS feed

  • Question

  • Hi All,

    Referring to this video https://www.youtube.com/watch?v=nJ7LzwiSwnw,

    My requirement is to show sum of amount after rows are combined .

    eg 

    Pedro China 63
    Pedro India 268
    Pedro US 239

    output,

    Pedro     China,India,US  570   

    i am able to combine the rows after watching this link https://www.youtube.com/watch?v=nJ7LzwiSwnw,
    but not sure how to aggregate the amount to get 570 as total amount

    Thursday, July 12, 2018 4:59 PM

Answers

  • Hey Kartic!

    Thanks for posting the question here and for watching the video. When you're in the Group By window, try setting things up as shown in the next image:

    

    that should create a new column called "Total Amount" that gives you exactly the number that you're searching for.

    Friday, July 13, 2018 1:10 AM

All replies

  • Hi Karic,

    you can group on the first column and then combine the values from the 2nd and sum up the values from the 3rd column: 

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhNKcpX0lFyzsjMSwTSZsZKsToIYc+8lEyQsJGZBYp4aDBI0NhSKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Combine", each Text.Combine(_[Column2], ","), type table}, {"Sum", each List.Sum([Column3]), type number}})
    in
        #"Grouped Rows"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, July 12, 2018 8:00 PM
  • Hey Kartic!

    Thanks for posting the question here and for watching the video. When you're in the Group By window, try setting things up as shown in the next image:

    

    that should create a new column called "Total Amount" that gives you exactly the number that you're searching for.

    Friday, July 13, 2018 1:10 AM
  • How does it work on bigger data set :

    eg 

    Pedro China 63.24
    Pedro US 166.96
    Pablo Mexico 314.61
    Rob Panama 178.26
    Alberto Panama 117.22
    Alberto Canada 216.71
    Miguel Mexico 169.26
    Bill UK 121.94
    Ken Italy 57.95
    Miguel Australia 129.72
    Pedro Panama 54.46
    Alberto UK 169.03
    Pedro Canada 46.96
    Bill UK 25.69
    Ken Mexico 34.99
    Marco Mexico 175.7
    Ken China 193.19
    Pedro UK 141.84
    Pablo Australia 313.64

    the code you gave is only working for 3  rows which i gave intially , not on bigger dataset

    Friday, July 13, 2018 8:07 AM
  • Hi Kartic21. The code/examples provided above should work on a larger dataset. For Imke's example, simply replace the source step (which hard-codes the sample dataset you provided) with your actual data.

    Please give that a try and let us know if it works for you.
    Ehren

    Thursday, July 19, 2018 12:04 AM