Percent of Total, Aggregated by Customer, then Ranked

[rdg515]

In Power Query I want to create a ranking of Percent of Total Margin by Customer.

In this step I'm aggregating Margin by Customer then trying to add another column for the Total Margin for the entire Source table

= Table.AddColumn( Table.Group(Source, {"Customer"}, {{"Margin", each List.Sum([Margin]), type nullable number}}), "Total Margin", List.Sum(Source[Margin]))

Unfortunately, I'm getting an error: 

Expression.Error: We cannot convert the value 18472392.070000324 to type Function.
Details:
Value=18472392.07
Type=[Type] 

[Anonymous]

only from a strictly syntactic point of view can I tell you that the third parameter of the Table.addcolumn function must be a function that expects the rows of the table as input.

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table

 Quindi questa:

= Table.AddColumn( Table.Group(Source, {"Customer"}, {{"Margin", each List.Sum([Margin]), type nullable number}}), "Total Margin", List.Sum(Source[Margin]))

dovrebbe cambiare in:

= Table.AddColumn( Table.Group(Source, {"Customer"}, {{"Margin", each List.Sum([Margin]), type nullable number}}), "Total Margin", (row)=> doSomethinkWithTheRow )

if you want specific help, post your starting data and desired data in an easily copyable form.

[ImkeF]

Hi @rdg515 ,

if my understanding is correct, all you have to do is to add the each keyword to your formula:

Table.AddColumn( Table.Group(Source, {"Customer"}, {{"Margin", each List.Sum([Margin]), type nullable number}}), "Total Margin", each List.Sum(Source[Margin])) 

[rdg515]

This solution does give the correct answer but it takes forever.  I was thinking it takes forever because it calculates a sum of the column for each row instead of just doing it once.  Conceptually I'd like to just sum up the entire column, then join that result as a new column to my summarized table and provide that result for every row.

The following code:

= Table.Group(Source, {"Customer"}, {{"Margin", each List.Sum([Margin])}})

Produces results that look like this:

Customer ID	Margin
a	200
b	350
c	250
d	-300

The next step I'm working on is to get this:

Customer ID	Margin	Total Margin
a	200	500
b	350	500
c	250	500
d	-300	500

I want the final result to look like this:

Customer ID	% of Total Margin	Rank
a	40%	3
b	70%	1
c	50%	2
d	-60%	4

This table will be used to create a whale curve which requires Customers IDs to be ranked on the x-axis (can't put a measure on the axis), and the values will be a Running Total of % of Total Margin.  The line chart will show an increase relative to customers that had positive margin, then a decrease for customers with negative margin, ending and reconciling at 100%. 

[Anonymous]

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMFCK1YlWSgKyjU0h7GSQOJSdAmTrGoMUxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, Margin = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Margin", Int64.Type}}),
    TotalM = Table.AddColumn(#"Changed Type", "TotalM", each List.Sum(#"Changed Type"[Margin])),
    pcTM = Table.AddColumn(TotalM, "pcTM", each [Margin]/List.Sum(#"Changed Type"[Margin]), type number ),
    #"Changed Type1" = Table.TransformColumnTypes(pcTM,{{"pcTM", Percentage.Type}}),
    rank = Table.AddColumn(#"Changed Type1", "rank", each List.Count(#"Changed Type"[Margin])-List.PositionOf(List.Sort(#"Changed Type"[Margin]),[Margin]))
in
    rank

[rdg515 · Accepted solution]

Thank you this is exactly what I was looking for!  I made a change to the ranking calculation because, while your suggestion preserved the order of the Customer IDs, the order wasn't important and this way performed much faster for me against my real data set.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMFCK1YlWSgKyjU0h7GSQOJSdAmTrGoMUxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, Margin = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Margin", Int64.Type}}),
    TotalM = Table.AddColumn(#"Changed Type", "TotalM", each List.Sum(#"Changed Type"[Margin])),
    pcTM = Table.AddColumn(TotalM, "pcTM", each [Margin]/List.Sum(#"Changed Type"[Margin]), type number ),
    #"Changed Type1" = Table.TransformColumnTypes(pcTM,{{"pcTM", Percentage.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"pcTM", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

[edhans]

@rdg515 - if one of these isn't the answer, can you post some sample data please, with expected results?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Greg_Deckler]

@rdg515 Seems like this would be straight-forward in DAX but not sure how to rank in Power Query but I am sure someone like @ImkeF and @edhans know.