Need help with list.Sum custom colomns

[Timo1980]

Hi All,

i have the following issue in Power Query i want to create a customer colomn ( i know i can do this with grouping or in Dax, but i need to to this in power query) Table 1 is the data and i want to create the colomns Total Revenue and Sales Percentage. For the Total Revenue i only want to take into account positive revenue. Any help would be appreciated!!

Customer	QTY	Revenue	Total Revenue	Sales Percentage
A	1	10	28	36%
A	2	18	28	64%
B	2	25	25	100%
C	2	23	23	100%
C	-2	-5	null	null
D	-1	-8	null	null
D	2	9	24	38%
D	2	15	24	63%

[Jimmy801 · Accepted solution]

Hello @Timo1980 

add a new column and enter this formula. The only thing you have to pay attention is that the variable ChangedType you have to replace with your prior step name.

List.Sum(Table.SelectRows(ChangedType, (sel)=> sel[Customer]=[Customer] and sel[Revenue]>=0)[Revenue])

here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYQOlWB0I1wjEtQBznaBcI1Mw1xnGNYZzdUF8XYi0C4gJMkzXAs4HSVui8AyBimMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, QTY = _t, Revenue = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"QTY", Int64.Type}, {"Revenue", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(ChangedType, "Total Revenue", each List.Sum(Table.SelectRows(ChangedType, (sel)=> sel[Customer]=[Customer] and sel[Revenue]>=0)[Revenue])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "%", each [Revenue]/[Total Revenue],Percentage.Type)
in
    #"Added Custom1"

and here is the output

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYQOlWB0I1wjEtQBznaBcI1Mw1xnGNYZzdUF8XYi0C4gJMkzXAs4HSVui8AyBimMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, QTY = _t, Revenue = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"QTY", type number}, {"Revenue", type number}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Customer"}, {{"Total Revenue", each List.Sum(List.Select([Revenue], (r)=>r>0)), type nullable text},{"rev", each Table.AddColumn(_, "%rev", (pc)=> pc[Revenue]/List.Sum(List.Select([Revenue], (r)=>r>0))) }} ),
    #"Tabella rev espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "rev", {"QTY", "Revenue", "%rev"}, {"QTY", "Revenue", "%rev"})
in
    #"Tabella rev espansa"

[HotChilli]

Duplicate the table.

Filter out the -ve figures from the Revenue column (you can use the Number filter feature from the column header dropdown).

Then select the Customer column, choose 'Group By' from the ribbon (Sum the Revenue column, give it a name 'Tot Rev').

This will give you a 2 column table with the +ve revenues summed.

--------------------

Go back to the original table.

Pick 'Merge Queries as new' from the ribbon.

Merge the original with the new table (created above) on Customer field. Use Inner Join.

Expand the column header to return only the 'Tot Rev' column.

Add a conditional column (from Menu), use the interface to mimic the code below

if Revenue < 0 then null else 'Tot Rev'

 Change the column type to be  whole number.

Then select the original Revenue Column and the new Group Revenue column.

Pick 'Add Column' from the menu->go to the From Number section->pick Standard-> Divide.

That should add a column with the right arithmetic.

I would leave that column as a decimal (i.e. don't change it to percenatge type- it'll make it easier in powerbi front end).

Let me know how you go.