Forum Discussion
Help with DAX for Running Total Subtractions
Hello bibro5
To obtain the correct row totals, we modify the formulas as follows:
Modify the "Available Quantity" formula as follows:
Available Quantity = IF ( [Total Quantity Sold] <> BLANK (), SUM ( Inventory[Quantity] ) )Modify the "Running Quantity Sold" formula as follows:
Running Quantity Sold = VAR _remainingQty = CALCULATE ( [Available Quantity] - [Total Quantity Sold], FILTER ( ALL ( Transactions ), Transactions[ID] = MAX ( Transactions[ID] ) && Transactions[DATE] <= MAX ( Transactions[DATE] ) ) ) VAR _runningQuntity = IF ( _remainingQty >= 0, [Total Quantity Sold], [Total Quantity Sold] + _remainingQty ) VAR _handlingOversale = IF ( _runningQuntity <= 0 && [Total Quantity Sold] <> BLANK (), 0, _runningQuntity ) RETURN IF(ISINSCOPE(Transactions[DATE]), _handlingOversale, [Available Quantity])In the following animation, you can observe that we are achieving the desired results at both the total level and the individual ID/Type leve. While I understand you do not wish any slicer, please note that the formula is robust to handle it should you decide to utilize one in the future.
I am also attaching the Power BI file for your reference.
I hope have answered your question correctly. However, if there is still any doubt, please do not hesitate to let me know.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Proud to be a Super User
quantumudit thank you with your reply and you are correct. I could have been more descriptive on what I wanted the outcome to be.
In my example, I don't want to use a slicer for each individual item. I want it to look like my screenshot. The only issue is the total for running quantity sold is greater than available quantity. can you help me fix this?
- quantumudit
Super User
Hello bibro5
To obtain the correct row totals, we modify the formulas as follows:
Modify the "Available Quantity" formula as follows:
Available Quantity = IF ( [Total Quantity Sold] <> BLANK (), SUM ( Inventory[Quantity] ) )Modify the "Running Quantity Sold" formula as follows:
Running Quantity Sold = VAR _remainingQty = CALCULATE ( [Available Quantity] - [Total Quantity Sold], FILTER ( ALL ( Transactions ), Transactions[ID] = MAX ( Transactions[ID] ) && Transactions[DATE] <= MAX ( Transactions[DATE] ) ) ) VAR _runningQuntity = IF ( _remainingQty >= 0, [Total Quantity Sold], [Total Quantity Sold] + _remainingQty ) VAR _handlingOversale = IF ( _runningQuntity <= 0 && [Total Quantity Sold] <> BLANK (), 0, _runningQuntity ) RETURN IF(ISINSCOPE(Transactions[DATE]), _handlingOversale, [Available Quantity])In the following animation, you can observe that we are achieving the desired results at both the total level and the individual ID/Type leve. While I understand you do not wish any slicer, please note that the formula is robust to handle it should you decide to utilize one in the future.
I am also attaching the Power BI file for your reference.
I hope have answered your question correctly. However, if there is still any doubt, please do not hesitate to let me know.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Proud to be a Super User