Calculating a % relative to a previous row / Referencing other rows in calculation

SimonCJ

SimonCJ

New member
Joined
Nov 13, 2019
Messages
1
Reaction score
0
Points
0
Excel Version(s)
2016
I have a PowerPivot data model with spend data by year for 4 years for approximately 1 million customers. I'm looking to use Power Query to transform the data to calculate the % of previous year spend achieved for each customer for each year. I need to do this at a row level because I then want to be able to segment customers based on these %s. E.g. compare customers who spent =>80% of what they spent last year with customers who spent <80% of what they spent last year.

This is how the data is structured. The fourth column is what I'm hoping to add:

Customer IDYearSpendSpend as % of last year spend
10012015£2,000NULL
10012016£1,00050%
10012017£1,500150%
10022015£100NULL
10022016£200200%
10022017£15075%
10032015£10,000NULL
10032016£1,00010%
10032017£12,000120%


Thank you in advance for any help and guidance!

Simon
 
If source data is already always sorted Year within Customer ID, then merging the table onto itself using two indexes should work

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Customer ID", "Year", "Spend"}, {"Customer ID.1", "Year.1", "Spend.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Index.1", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Customer ID]=[Customer ID.1] and [Year]=[Year.1]+1 then [Spend]/[Spend.1] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Customer ID.1", "Year.1", "Spend.1"})
in
    #"Removed Columns"
 
This would work regardless of sort, but I think the above method would be more efficient for larger data sets if original data came in sorted

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", (i) => List.Sum(Table.SelectRows(Source, each ([Customer ID] = i[Customer ID] and [Year]+1 = i[Year]))[Spend]) , type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Spend as % of last year spend", each [Spend]/[Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
    in #"Removed Columns"
 
Hi,

my solution is mor or less equl to #2 of horseyride. the solution of #3 does prodcude unfortunatelly "Error" in the calcvulated column.
 

Attachments

  • Xlguru - Calculating a percentage relative to a previous row - Referencing other rows in calcul.xlsx
    24.9 KB · Views: 17
You have to have Year and Spend as numbers for it to work For your file...

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,",","",Replacer.ReplaceText,{"Spend"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","£","",Replacer.ReplaceText,{"Spend"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Year", type number}, {"Spend", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (i) => List.Sum(Table.SelectRows(#"Changed Type", each ([Customer ID] = i[Customer ID] and [Year]+1 = i[Year]))[Spend]) , type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Spend as % of last year spend", each [Spend]/[Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
 in #"Removed Columns"
 
Hello horseyride,

yeah, you're right. That's how it works now. I had a little hope that the addressing method you used was faster than the "normal" access with an index. But unfortunately this was not the case. With 10,000 lines the calculation took about 2 minutes. The method with the join is of course, as expected, very fast. And although I am not the questioner, I learned that I can define the join directly in the query. So far I have always taken the detour "Load as connection" and "Merge". But it's much more elegant that way.
 
Can I ask why you want to use Power Query and not DAX measures for this?
 
You must log in or register to reply here.
Back
Top