Forum Discussion

jbajon's avatar
jbajon
Frequent Visitor
5 years ago
Solved

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%
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    • jbajon's avatar
      jbajon
      Frequent Visitor
      5 years ago

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

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    5 years ago
    _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)
    • jbajon's avatar
      jbajon
      Frequent Visitor
      5 years ago

      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.