Forum Discussion

cpdanielmc21's avatar
cpdanielmc21
Icon for Helper I rankHelper I
6 years ago
Solved

Running Total solution for slicer

Hi community I've been trying to apply solutions proposed in this forum for similar issues to mine, but have not been successful so far.   I want to create either a measure or a column that can he...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Ok.  I played with this some more.  Please try this expression.  Having account in both the table and the slicer makes this a challenge. 

     
    RunningTotal = var thisdate = CALCULATE(MAX(Total[Date]), ALLSELECTED(Total[Account]))
    return CALCULATE(SUM(Total[Amount]), ALLEXCEPT(Total,Total[Account]), Total[Date] <= thisdate)
     

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Yes.  You can just take the VALUES( ) clause out of the CALCULATE().  You only had one account in the example data, so assumed (incorrectly) you would want it that way.

 

Regards,

Pat

 

cpdanielmc21's avatar
cpdanielmc21
Icon for Helper I rankHelper I
6 years ago

Hi mahoneypat 

That's what I thought, so I erased that line, but get the following result (no filters applied):

Running total is weird now, and If suppose, I apply filter month "2" and no filer on account, running total should be 62, but shows 47 on last line. If i happen to also add filter Account "102", the running total now should be 12, but nope, it shows 47.

 

😞

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Try writing a new measure that references the original measure. This one will iterate over each account and add all of the running totals together. See if that works instead. Typing on a tablet. Sorry. 

     

    New = Sumx(values(table[account]), [original  measure])

     

    If not will look more later. 

     

    Pat

    • cpdanielmc21's avatar
      cpdanielmc21
      Icon for Helper I rankHelper I
      6 years ago

      mahoneypat 

      Thanks Pat, but still getting same result, 😞

      I just returned the original formula for running total and created the new measure, but it gives same numbers

      NewMeasure = SUMX(VALUES('Table'[Account]),[RunningTotal])
       
      RunningTotal =
      VAR thisdate =
      SELECTEDVALUE ( 'Table'[Date])
      RETURN
      CALCULATE (
      SUM ( 'Table'[Amount]),
      ALL('Table'),
      VALUES('Table'[Account]),
      'Table'[Date]<= thisdate
      )
      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        The reason you are seeing the weird #s is because the max date for Account 102 is less than the max for Account 101.  You need to get the max date in the selected month in the variable first with this type of change.

         

        RunningTotalAll = var thisdate = CALCULATE(MAX(Total[Date]), ALL(Total), VALUES(Total[Month]))
        return CALCULATE(SUM(Total[Amount]), ALL(Total), Total[Date]<= thisdate)
         
        Regards,
        Pat