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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jbajon
Frequent Visitor

Unexpected Running Total

How do I create a running (cumulative) sum of column C if column C = Column A divided by Column B. The problem seems to be that whenever a YTD function or Calculate with <= dates, it recalculates the YTD value for Column A and YTD for Column B then divides them, which in this case is not the same as a running sum of Column C
 
extras: Column A is a measure with SUM([Field Name]), Column B is measure with SUM([Field Name]).

DateColumn AColumn BColumn C

1/2020

7545961.63%
2/20206645961.44%
3/20206846121.47%
4/20205345951.15%
5/20204645531.01%
6/20208844941.96%
7/20207644371.71%
8/20208344061.88%
9/20207443941.68%
10/20208446201.82%
11/20208748411.80%
12/20207548231.56%
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jbajon , Try a measure like

calculate(sumx(values(Table[date]), calculate(divide(sum(Table[A]),Sum(Table[B])))), filter(AllSelected(Table), Table[date] <=max(Table[date])))

View solution in original post

4 REPLIES 4
FarhanAhmed
Community Champion
Community Champion

_Test 2 = 

var a = CALCULATE(SUM('Table'[Column A]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))
var b = MAXX('Table',MAX('Table'[Column B]))
Return 
DIVIDE(a,b)






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

Proud to be a Super User!




Hi FarhanAhmed,

 

Thank you for your response. When attempting your solution I was getting an error message that MAX() only accepts column references where [Column B] is actually a measure in this example.

amitchandak
Super User
Super User

@jbajon , Try a measure like

calculate(sumx(values(Table[date]), calculate(divide(sum(Table[A]),Sum(Table[B])))), filter(AllSelected(Table), Table[date] <=max(Table[date])))

Hi amitchandak,

Thank you for your reponse. I was able to alter your example a bit to get to my final working solution. The only difference I forgot to mention on my actual project that the Year and Month are two different fields (rather than combined into one field)  for formatting reasons, so I added an additional filter condition so that the running value restarted each year.

Here is what ultimately worked for me:

calculate(sumx(values('DateTable'[FullDate]), [Column_C]), filter(allselected('DateTable'), 'DateTable'[FullDate_Year] = max('DateTable'[FullDate_Year]) && 'DateTable'[FullDate_Month#] <=max('DateTable'[FullDate_Month#])))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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