Running Sum of values in same column before current record

[PurdiMark]

Hey Community,

I am looking for a DAX way of performing an Excel SUMIF() that looks at all cells in its column from row 1 to the row above; EG in cell C4 you would have SUMIF(A:A,A1,$C1:C3) in cell C52 you would have SUMIF(A:A,A1,$C1:C51).

That value would then need to be encased in an IF() statement to return 0 if the value = [SO Qty], otherwise the MIN() of (qty in [SO] minus 'the value') OR qty in [Stock].

I tried generating a measure to total the column and use that in a DAX formula but i get a circular error.

I have an [Index] column (1 to ...) that can be used to identify data rows prior to the current row.

The closest i have gotten is this formula;

=var RunningQty = CALCULATE(sum(Stock[Quantity]),filter(Stock,[index]<[index])) return if(RunningQty = Stock[Sales Orders],0,min((Stock[Sales Orders]-RunningQty),Stock[Quantity]))

But that is not correct as it just repeats the number in [Sales Orders]

The purpose of this column is to identify the quantity of each product/batch that is needed to fulfill outstanding orders. It does this by checking how much of the required amount has been allocated and working out how much can be allocated for that row: 0 (its fulfilled), the whole quantity of stock for that product/batch OR the required amount minus already allocated

[technolog · Accepted solution]

First, for the "Average CSAT per skill", you can use a relationship between the two tables based on the SKILL column. Once you've established that relationship, you can create a measure to calculate the average CSAT for each skill. The DAX formula would look something like this:

Average CSAT =
DIVIDE(
SUM(Table2[CSAT]),
COUNTROWS(Table1)
)
This formula assumes that your second table (which you haven't provided) has a column named CSAT which contains the CSAT scores. The formula divides the total CSAT from the second table by the number of rows in the first table, giving you the average CSAT per skill.

Now, for the running sum of values, you're trying to replicate the behavior of Excel's SUMIF function in DAX. The formula you provided is on the right track, but let's adjust it a bit to make it work correctly.

Running Sum =
VAR RunningQty =
CALCULATE(
SUM(Stock[Quantity]),
FILTER(
ALL(Stock),
Stock[Index] < EARLIER(Stock[Index])
)
)
RETURN
IF(
RunningQty = Stock[Sales Orders],
0,
MIN(Stock[Sales Orders] - RunningQty, Stock[Quantity])
)
The key change here is the use of the EARLIER function. This function allows you to reference a value from an earlier row context, which is what you're trying to achieve with the Excel SUMIF function. The formula calculates the running sum of the Quantity column for rows with an index less than the current row's index. It then checks if this running sum is equal to the Sales Orders value for the current row. If it is, it returns 0. Otherwise, it returns the smaller of two values: the difference between Sales Orders and the running sum, or the Quantity for the current row.