Forum Discussion

sloane's avatar
sloane
Frequent Visitor
5 years ago
Solved

Need Help with Filters on a running sum

Hi everyone,    I have a table that looks something like this;  and I want to be able to create a running total of the amount column and have two slicers controlling the output. The first sli...
Need Help
Tips and Tricks
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi sloane ,

     

    Create a measure as below:

     

    Measure = IF(ISFILTERED('Table'[State]),
 CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[State]=SELECTEDVALUE('Table'[State]))),
 CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date]))))

     

    And you will see:

     

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

sloane , Try like

Amount Targeted =

CALCULATE (
SUM(Table[amount]),
FILTER (
ALLSELECTED(Table),
( Table[Date] <= max(Table[Date])

)
))

  • sloane's avatar
    sloane
    Frequent Visitor
    5 years ago

    Thanks but this still doesnt work when I filter the state slicer. It only works when I filter the country slicer

    • v-kelly-msft's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi sloane ,

       

      Create a measure as below:

       

      Measure = IF(ISFILTERED('Table'[State]),
 CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[State]=SELECTEDVALUE('Table'[State]))),
 CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date]))))

       

      And you will see:

       

       

      For the related .pbix file,pls see attached.

       

      Best Regards,
      Kelly

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