Forum Discussion
Help Needed: Replicating Recursive Excel Depletion Logic in DAX (Running Total with Conditional Sub)
Hi Sasi_2108 ,
Thanks for clearing that up, that’s exactly how the solution works. It doesn’t just match totals at the Key level; it actually splits the AddPriorityQty across each line item in order, based on the running Pre-alloc quantity. For each Key, the model goes row by row, checks how much of the priority quantity has already been used, and then gives each row either its Pre-alloc amount or whatever’s left. This way, the allocation happens line by line and stops when the pool runs out, just like the Excel logic the business is used to.
Hi Sasi_2108 ,
Excel relies on row-by-row recursion, a calculation style that DAX does not support. In DAX, the same outcome is achieved by shifting away from a procedural approach and instead using a set-based, cumulative evaluation. Rather than referencing the immediately preceding row, DAX evaluates all rows up to the current one within the same key and defined order, and derives the remaining quantity from that accumulated context. As soon as the cumulative demand exceeds the available priority quantity, the allocation naturally falls to zero. As a result, the helper columns often needed in Excel, such as running totals, flags, or partial indicators, are no longer necessary in DAX, because these outcomes emerge implicitly from filter context. In short, Excel-style recursion is replaced by cumulative filtering in DAX, producing the same results without recursion.
Hi SShirivolu, thanks for your efforts and response. if you see the excel results of the below columns, values should exactly match with the table I provided. basically the business wants to split ADD Priority Qty across the different sales items based on Pre-alloc quantities and running quantities so its like distributing. for example, in the first key, 46 is ADD Priority Qty if you sum Total Add Priority same 46 would be there which means 46 is distributed across 11 rows.
| Flag and Qty to add Priority | Partial Qty | Total Add Priority |
Hi Sasi_2108 ,
This can be achieved in Power BI without true recursion by translating the Excel-style row-by-row depletion into ordered cumulative logic. After establishing a clear row sequence within each Key, the model calculates how much Pre-alloc quantity has already been consumed by prior rows, which allows it to determine how much of the Add Priority pool remains before each row is processed. Each row then receives the lesser of its Pre-alloc amount or the remaining pool, ensuring allocation follows the correct order and automatically stops once the pool is exhausted. The final 'Total Add Priority' values match the Excel results exactly, with no row exceeding its Pre-alloc and the total distributed per Key equaling the original Add Priority quantity, as demonstrated in the attached PBIX file.
Please find the attached .pbix file for your reference.Thank you so much for your efforts again. Here the problem statement is AddPriorityQty should be splitted across each line items. the Total Add Priority is matched with AddPriorityQty but the real stuff the business is looking for is to split the total values across each line item based on the running qty.
Hi Sasi_2108 ,
Thanks for clearing that up, that’s exactly how the solution works. It doesn’t just match totals at the Key level; it actually splits the AddPriorityQty across each line item in order, based on the running Pre-alloc quantity. For each Key, the model goes row by row, checks how much of the priority quantity has already been used, and then gives each row either its Pre-alloc amount or whatever’s left. This way, the allocation happens line by line and stops when the pool runs out, just like the Excel logic the business is used to.