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 slicer is country which shows only the states in the country when triggered 

the second slicer (where the magic happens) is for state, and gives the sum for the respective state

 

My current formula (below) only returns the sum on a country level but not a state level

 

 

Amount Targeted = 
VAR endOfPeriod = MAX ( 'Calendar'[Date] )

VAR startOfPeriod = MIN( 'Calendar'[Date] )

RETURN
    CALCULATE (
        SUM(Table[amount]),
        FILTER (
            ALL(Table),
               ( Table[Date] <= endOfPeriod
                         
        )
    ))

 

 

 Thank you in advance for your help 🙂 

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!

     

3 Replies

  • 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!