Search code examples
excelexcel-formulapowerquery

Spill formula to lookup different sections of table based on two arguments

I have a table of data that I need to do a lookup on (exact match or next smaller item), but the lookup needs to only look at a small section of the table based on the Vendor and Source columns.

The table is the result of PowerQuery and is called GroupedData. It's sorted by Vendor, Source and Days.

What I would like to do is:

  • For each combination of Vendor/Source I calculate the total Count of Orders.
  • Calculate 95% of the totals.
  • Lookup which day that 95% value was reached (i.e. look up on the Running Total column and return from the Days column).

For example:

  • The total for V1/SA is 102.
  • 95% of 102 is 96.9.
  • The lookup will return 4 days as Count of Orders = 11+52=63 is less than the 95%, while 11+52+35=98 is more.

I'd be happy with either a formula or power query answer if possible.

Vendor Source Days Count of Orders Running Total
V1 SA 3 11 11
V1 SA 4 52 63
V1 SA 5 35 98
V1 SA 6 3 101
V1 SA 7 1 102
V1 SB 2 10 10
V1 SB 3 15 25
V1 SB 5 100 125
V1 SB 6 3 128
V1 SB 7 2 130
V2 SA 8 1 131
V2 SA 9 1 132
V2 SB 5 428 428
V2 SB 6 5,226 5,654
V2 SB 7 15,800 21,454
V2 SB 8 13,713 35,167
V2 SB 10 8,529 43,696
V2 SC 2 3,618 3,618
V2 SC 3 2,856 6,474
V2 SC 4 941 7,415
V2 SD 1 669 669
V2 SD 2 520 1,189
V2 SD 3 392 1,581

My results table at the moment looks like this:
Results Table

The formula used in results so far:

  • H2 =UNIQUE(GroupedData[[Vendor]:[Source]])
  • J2 =MAXIFS(GroupedData[Days],GroupedData[Vendor],INDEX($H$2#,,1),GroupedData[Source],INDEX($H$2#,,2))
  • K2 =SUMIFS(GroupedData[Count of Orders],GroupedData[Vendor],INDEX($H$2#,,1),GroupedData[Source],INDEX($H$2#,,2))
  • M2 =K2#*95%

Formula I've tried:

Returns #VALUE! for each value

=LET(LookupArray,XLOOKUP(INDEX($O$4#,,1) & INDEX($O$4#,,2),GroupedData[Vendor] & GroupedData[Source],GroupedData[Running Total],,-1):XLOOKUP(INDEX($O$4#,,1) & INDEX($O$4#,,2),GroupedData[Vendor] & GroupedData[Source],GroupedData[Running Total],,-1,-1),
     ReturnArray, XLOOKUP(INDEX($O$4#,,1) & INDEX($O$4#,,2),GroupedData[Vendor] & GroupedData[Source],GroupedData[Days],,-1):XLOOKUP(INDEX($O$4#,,1) & INDEX($O$4#,,2),GroupedData[Vendor] & GroupedData[Source],GroupedData[Days],,-1,-1),
     Result, XLOOKUP($T$4#,LookupArray,ReturnArray),
     Result)

Returns a single #CALC! error:

=BYROW(O4#,
       LAMBDA(MyArray,
              LET(LookupArray,XLOOKUP(INDEX(MyArray,,1) & INDEX(MyArray,,2),GroupedData[Vendor] & GroupedData[Source],GroupedData[Running Total],,-1):XLOOKUP(INDEX(MyArray,,1) & INDEX(MyArray,,2),GroupedData[Vendor] & GroupedData[Source],GroupedData[Running Total],,-1,-1), 
                  ReturnArray, XLOOKUP(INDEX(MyArray,,1) & INDEX(MyArray,,2),GroupedData[Vendor] & GroupedData[Source],GroupedData[Days],,-1):XLOOKUP(INDEX(MyArray,,1) & INDEX(MyArray,,2),GroupedData[Vendor] & GroupedData[Source],GroupedData[Days],,-1,-1), 
                  Result, XLOOKUP($T$4#,LookupArray,ReturnArray,,-1), 
                  Result)))

Bit of waffle that may be relevant:
=XLOOKUP(INDEX(H2#,,1) & INDEX(H2#,,2),GroupedData[Vendor] & GroupedData[Source],GroupedData[Running Total],,-1) returns all the first values in the Running Total. Using a Search Last to First parameter returns all the last values for each group.

=XLOOKUP(H2 & I2,GroupedData[Vendor] & GroupedData[Source],GroupedData[Running Total],,-1):XLOOKUP(H2 & I2,GroupedData[Vendor] & GroupedData[Source],GroupedData[Running Total],,-1,-1) returns all the Running Totals for the first group.

=XLOOKUP($M$2,XLOOKUP(H2 & I2,GroupedData[Vendor] & GroupedData[Source],GroupedData[Running Total],,-1):XLOOKUP(H2 & I2,GroupedData[Vendor] & GroupedData[Source],GroupedData[Running Total],,-1,-1),XLOOKUP(H2 & I2,GroupedData[Vendor] & GroupedData[Source],GroupedData[Days],,-1):XLOOKUP(H2 & I2,GroupedData[Vendor] & GroupedData[Source],GroupedData[Days],,-1,-1),,-1) works for a single group, but I'd need to drag down rather than let it spill.

Any help is greatly appreciated.

Solution

  • In powerquery this pulls the highest Running Total up until 95% of the total volume

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Vendor", "Source"}, {{"data", each _, type table}, {"All", each List.Max([Days]), type number}, {"Volume", each List.Sum([Count of Orders]), type number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "95% Vol", each .95*[Volume]),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "95%",(x)=>List.Last(Table.SelectRows(x[data], each [Running Total] <= x[Volume]*.95)[Running Total])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"data"})
in  #"Removed Columns"

    enter image description here