Simple Formual needed - Power Query New Custom Column - Refer to other cells

Hi Schwadenfeld ,

You can use the Group by function in power query to do the following in Advanced settings: group by Date and Campaign Name, and add new columns which sums Ad Sales and adds all rows 
Then you can perform your calculations using custom columns.

See sample advanced editor query that does all the steps for you (just copy and paste it in your power query editor, and amend as needed to match your table name): 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3MNE3MjAyUtJRcgTigKL8lNLkEgUQ29BCz8QCSBvrmZspxergVuxEimJnbIoNkFU7oak2MtAzNkFWbWiAw2wXbGajqHZG86OJgZ4RimJDHIrdQEYDHYLLi8iOjgCyLfVwOxlZbRhYrTFON7iQGykgtrGZnqUZXHEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Campaign Name" = _t, #"Purchased ASIN" = _t, #"Ad Sales" = _t, Spend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Campaign Name", type text}, {"Purchased ASIN", type text}, {"Ad Sales", type number}, {"Spend", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Campaign Name"}, {{"Count", each _, type table [Date=nullable date, Campaign Name=nullable text, Purchased ASIN=nullable text, Ad Sales=nullable number, Spend=nullable number]}, {"sum Ad sales", each List.Sum([Ad Sales]), type nullable number}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Purchased ASIN", "Ad Sales", "Spend"}, {"Purchased ASIN", "Ad Sales", "Spend"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Ratio of Ad Sales", each [Ad Sales]/[sum Ad sales]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Attributed Ad Spend", each [Ratio of Ad Sales] * [Spend]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"sum Ad sales"})
in
    #"Removed Columns"

sample output: