cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

@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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
FarhanAhmed
Super User II
Super User II

_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 IV
Super User IV

@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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors