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 ,
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.Hi Sasi_2108 ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Hi Sasi_2108 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions