Calculate sum of values in one column based on values in another column (SUMIF / SUMPROD

[YardOfTheYard]

Hi all, I am creating an inventory outlook model. I have shaped my data in PQ. For each SKU/partnumber, have a Stock figure for each week in the next 12 weeks, and I have a Sales volume for each week in the next 12 weeks.

For each week, I want to calculate:

i. the average Sales for the next 4 weeks

ii. therefore, the coverage as calculated by Stock divided average-4-week-sales

My query looks like this:

In an Excel Table, I would create:

a. a column with a SUMIFS to calculate, for the rows where the RowType3 is "Stock", the Average of the values in the Value column where the SKU is the same, and the tblWks.WC column date is between the current row date and that+28 days.

=IF([@RowType3]="Stock",AVERAGEIFS([Value],[RowType3],"Sales",[tblWks.WC],">"&[@[tblWks.WC]],[tblWks.WC],"<="&[@[tblWks.WC]]+28),0)

Example result: for the Stock on 02/09, the average sales of the next 4 weeks (the average of 135,416,107,107) would be 191.

b. a column to divide, for the rows where the RowType3 is "Stock" and the SKU is the same, the Value divided by the previously-created Average sales column

=IF([@RowType3]="Stock",[@Value]/[@Avg4WkSales],0)

Example result: for the Stock on 02/09, the stock is 868, and 868/191 = 4.5 weeks of cover

How do I achieve this in PowerQuery please? I'm sure it's a combination of filter/grouping, but I can't get my head around it!

Notes:

1. Ideally I want this calculation to be within the DataModel as a measure/DAX, so that the calculation can be made at the level of SKU or, when relationships are created, at other levels of aggregation.

2. The data set is much larger than this, of course.

3. The arbitary "4" weeks is something I would want to flex later, but for now I look simply for the approach!

[SBAJW]

Thanks @Anonymous for the suggested solution. I figured out how to insert the M-Code, but I wonder if this is an inefficient solution - the query now takes over a minute to refresh, and this is only for a small subset of the data that I used for this forum post. 

[YardOfTheYard]

Hi, I paste a stripped down example table here, if this makes it easier to answer? Here there are just two Shipping Codes, one SourceFileName and six weeks of data for Sales and Stock (to keep the example small).

The last two columns are the results I imagine I want (as achieved in Excel), but I am open to any solution.

SourceFileName	Shipping code	RowType3	tblWks.WC	Value	Avg4WkSales	StockCover
DF09	1564	Sales	02/09/2024	0	91	0.0
DF09	1564	Sales	09/09/2024	203	91	0.0
DF09	1564	Sales	16/09/2024	162	50	0.0
DF09	1564	Sales	23/09/2024	0	70	0.0
DF09	1564	Sales	30/09/2024	0	94	0.0
DF09	1564	Sales	07/10/2024	37	141	0.0
DF09	1564	Sales	14/10/2024	245	245	0.0
DF09	1564	Stock	02/09/2024	895	91	9.8
DF09	1564	Stock	09/09/2024	692	91	7.6
DF09	1564	Stock	16/09/2024	530	50	10.7
DF09	1564	Stock	23/09/2024	560	70	7.9
DF09	1564	Stock	30/09/2024	560	94	6.0
DF09	1564	Stock	07/10/2024	523	141	3.7
DF09	1564	Stock	14/10/2024	278	245	1.1
DF09	1576	Sales	02/09/2024	590	312	0.0
DF09	1576	Sales	09/09/2024	135	191	0.0
DF09	1576	Sales	16/09/2024	416	316	0.0
DF09	1576	Sales	23/09/2024	107	232	0.0
DF09	1576	Sales	30/09/2024	107	274	0.0
DF09	1576	Sales	07/10/2024	634	358	0.0
DF09	1576	Sales	14/10/2024	83	83	0.0
DF09	1576	Stock	02/09/2024	868	312	2.8
DF09	1576	Stock	09/09/2024	733	191	3.8
DF09	1576	Stock	16/09/2024	321	316	1.0
DF09	1576	Stock	23/09/2024	744	232	3.2
DF09	1576	Stock	30/09/2024	685	274	2.5
DF09	1576	Stock	07/10/2024	81	358	0.2
DF09	1576	Stock	14/10/2024	461	83	5.6

[Anonymous · Accepted solution]

Hi @YardOfTheYard ,

Is there something wrong with your expected result? You need to calculate the average value for the next 4 weeks, so the Avg corresponding to the last date of each sales segment should not exist. It seems that you should move up one data point from the second date?

Here is my solution:
Use this M code to create a custom column:

let 
   current_Shippingcode = [Shipping code],
   current_date = [tblWks.WC]
in 
   List.Average(
       Table.SelectRows(
       #"Changed Type",
       each [Shipping code] = current_Shippingcode and [RowType3] = "Sales" and [tblWks.WC] > current_date and [tblWks.WC] <= Date.AddDays(current_date, 28)
   )[Value]
)

And the output is as below:

Then use this M code to create another custom column:

if [RowType3] = "Sales" then 0 else [Value] / [Avg4WkSales]

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SBAJW]

Hi, really basic question, but I already have M-code for the query...

let
    Source = OverseasFUP,
    #"Filtered Rows3" = Table.SelectRows(Source, each [WkNumFUP] >= 202436 and [WkNumFUP] <= 202442),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows3", each ([Shipping code] = "300001564" or [Shipping code] = "300001576")),
    #"Extracted Last Characters" = Table.TransformColumns(#"Filtered Rows1", {{"Shipping code", each Text.End(_, 4), type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Extracted Last Characters", {"RowType1"}, tblRowTypes, {"RowType1"}, "tblRowTypes", JoinKind.LeftOuter),
    #"Expanded tblRowTypes" = Table.ExpandTableColumn(#"Merged Queries", "tblRowTypes", {"RowType3"}, {"RowType3"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded tblRowTypes", each ([RowType3] = "Sales" or [RowType3] = "Stock")),
    #"Merged Queries1" = Table.NestedJoin(#"Filtered Rows", {"WkNumFUP"}, tblWks, {"WkNumFUP"}, "tblWks", JoinKind.LeftOuter),
    #"Expanded tblWks" = Table.ExpandTableColumn(#"Merged Queries1", "tblWks", {"WC"}, {"tblWks.WC"}),
    #"Sorted Rows" = Table.Sort(#"Expanded tblWks",{{"Shipping code", Order.Ascending}, {"RowType3", Order.Ascending}, {"tblWks.WC", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"RowType1", "WkNumFUP"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"SourceFileName", "Shipping code", "RowType3", "tblWks.WC", "Value"})
in
    #"Reordered Columns"

I don't understand where to paste the M-code you mentioned?

[SBAJW]

I assume you mean to paste it in as a nested "let...in"......but it seems to want a token comma ","

I am lost!

Thanks