Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
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.
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?
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.
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.
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.
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!
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
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.
Proud to be a Super User! | |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
80 | |
69 | |
60 |