Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
MRCA
Frequent Visitor

Running Total in a Matrix - Reset at negative and set to 0. Continue once positive value is added.

Hi,

I need help with the following issue when I try to create a meause for Running Total and trying to visualise it via a Matrix.
The goal is to have a running total of Forecast - Supply and when it reaches a negative value to set to 0.
The difficult part is I want it to continue the running total again once positive supply is added.
Please let me know if you have question. Appreciate any help

MRCA_0-1736309077344.pngMRCA_1-1736310747657.png

 

8 REPLIES 8
MRCA
Frequent Visitor

Hi @rajendraongole1 

Thank you for the reply and help.

I tried your above code but when I build it into my demo PBX the number displayed a incorrect running total and very high comparetively. Screen below for reference.

MRCA_0-1736373381403.png

MRCA_0-1736372430905.png
I am also linking my PBX test for reference to make this easier to read.
https://www.dropbox.com/scl/fi/jbg5uss48vl9vatjbvd8p/RunningTotalPBITTest.pbix?rlkey=rlt51k7y6q3xqvq... 

Thanks!

Hi, @MRCA 

 

What do you expect the output to be?

vzhangtinmsft_0-1736405620568.png

 

Best Regards,

Community Support Team _Charlotte

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

 

Hi @MRCA - can you please check the attached pbix file.

 

rajendraongole1_0-1736398940822.png

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Hi @rajendraongole1 

Thanks for the PBIX file and I have tested it again and it is not the exact intended result I require.
Basically, I wish to calculate the result by Supply-Forecast than add the result to the next week and continue the running total. When the result becomes negative I want it to show 0 as per your PBIX file. However, When the next Supply comes in to do the same of Supply - CurWeek Forecast (Not including previous results) and than if it is positive to continue the running total.
I have attached an excel demo of the result I wish with the formula. That is the view I intended to try to replicate in PBI but it keep the running total for the previous week even though it displays 0 on the matrix if that makse sense.

MRCA_0-1736417186554.png

https://www.dropbox.com/scl/fi/11jsr9zit5t12vgbnwa32/RunningTotalExcel.xlsx?rlkey=0ggy7h7uim6qlhbxv1... 

Thanks!

Hi @MRCA  - It seems like you've explained the expected behavior, and the attached file provides an example of your desired result. Since the calculation involves a combination of running totals and conditional logic, I can provide a formula or approach that matches your intended result.

 

Intended R =
VAR CurrentWeek = MAX(Table[Week])
VAR CurrentSupply = MAX(Table[Supply])
VAR CurrentForecast = MAX(Table[CF&G])
VAR PreviousWeeks =
FILTER(
ALL(Table),
Table[Week] < CurrentWeek
)
VAR RunningTotalPrev =
SUMX(
PreviousWeeks,
MAX(Table[Supply]) - MAX(Table[CF&G])
)
VAR CurrentResult = CurrentSupply - CurrentForecast
VAR FinalResult =
IF(RunningTotalPrev + CurrentResult < 0, 0, RunningTotalPrev + CurrentResult)
RETURN
FinalResult

 

let me know, and I can refine the logic further.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Hi @rajendraongole1 

I have adjusted the last formula you provided a little as the result was showing very high numbers when using SUMX with MAX. 
However, With my adjustment it is very close to the intended result. Now it correctly resets after a negative value once a new positive supply is received I.E week 27/01/2025.
The problem now is that it does not carry this postitive supply to the following week of 3/02/2025 but only check current week supply vs forecast.
The result for 3/02/2025 should be last week result if it is positive (200) + Supply (200) - Forecast(100) = 300 but instead it is only doing Supply(200) - Forecast(100)=100
Screenshot below and PBIX link as well.
Thanks!

MRCA_0-1736459744977.png

https://www.dropbox.com/scl/fi/xsyxylbd2gyasia5kwra3/RunningTotalPBITTest-1.pbix?rlkey=ko9z3z4y8alyv... 

MRCA
Frequent Visitor

Hi @rajendraongole1 

Hope you can help based on my latest results from my last comment.
Please let me know if you need further information.

Thanks

rajendraongole1
Super User
Super User

Hi @MRCA  - you can create a DAX measure as below:

Running Total Reset =
VAR CurrentDate = MAX('Table'[Week]) -- Replace with your date or week column
VAR AllDates = ALL('Table'[Week]) -- All available weeks/dates
VAR FilteredDates = FILTER(
AllDates,
'Table'[Week] <= CurrentDate
)
VAR RunningCalc =
SUMX(
FilteredDates,
MAX('Table'[Forecast]) - MAX('Table'[Supply])
)
VAR ResetToZero =
IF(RUNNINGCALC < 0, 0, RUNNINGCALC)

RETURN
ResetToZero

 

Add this measure to your matrix visualization.Use the Week/Date column in rows and the measure Running Total Reset in values.

This helps to resolve the same, please replace the tablenames as per your model.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.