Results 1 to 7 of 7

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

  1. #1
    Neophyte SimonCJ's Avatar
    Join Date
    Nov 2019
    Posts
    1
    Articles
    0
    Excel Version
    2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 ID Year Spend Spend as % of last year spend
    1001 2015 2,000 NULL
    1001 2016 1,000 50%
    1001 2017 1,500 150%
    1002 2015 100 NULL
    1002 2016 200 200%
    1002 2017 150 75%
    1003 2015 10,000 NULL
    1003 2016 1,000 10%
    1003 2017 12,000 120%


    Thank you in advance for any help and guidance!

    Simon

  2. #2
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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"

  3. #3
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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"

  4. #4
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    20
    Articles
    0
    Excel Version
    Office 365
    Hi,

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

  5. #5
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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"

  6. #6
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    20
    Articles
    0
    Excel Version
    Office 365
    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.

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,681
    Articles
    0
    Excel Version
    O365
    Can I ask why you want to use Power Query and not DAX measures for this?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •