Forum Discussion

bossamus's avatar
bossamus
Frequent Visitor
5 years ago
Solved

Calculating Conditional Running Totals

DateNameFruitQuantityFruit Running Total
1/1/2020FredApple11
1/1/2020GeorgeOrange11
1/2/2020FredOrange22
1/5/2020FredOrange57
1/7/2020GeorgeOrange12
1/10/2020FredApple34

 

Hi there and thank you for reading!

 

I am looking for a way to calculate conditional running totals. I am asking for help writing a formula that will calculate the 'Fruit Running Total' based on who the person is and what fruit they purchased on that date.

 

So for the above example,

  • Fred gets 1 apple on the 1st, so his running total is 1. The next time he gets 3 apples on the 10th, so the running total should be 4.
  • George gets 1 orange on the 1st, running total is 1 and the next time he gets an orange on the 7th, his running total should be 2.
  • Fred gets 2 oragnes on the 2nd, running total is 2 and the next time he gets 5 oranges on the 5th, bringing his total to 7.

 

How would I calculate 'Fruit Running Total'?

 

Any help is appreciated!

Need Help
Tutorial Requests
  • edhans's avatar
    edhans
    5 years ago

    Here you go bossamus 

    Full measure which returns the same as your expected result column in your sample data.

     

    Running Total Measure = 
VAR varName = MAX('Table'[Name])
VAR varFruit = MAX('Table'[Fruit])
VAR varCurrentDate = MAX('Table'[Date])
VAR Result = 
    SUMX(
        FILTER(
            ALL('Table'),
            'Table'[Name] = varName
                && 'Table'[Fruit] = varFruit
                && 'Table'[Date] <= varCurrentDate
        ),
        'Table'[Quantity]
    )
RETURN
    Result

     

     

  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi bossamus ,

     

    You may also create a column as below:

    Fruit running total = SUMX(FILTER('Table','Table'[Fruit]=EARLIER('Table'[Fruit])&&'Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]<=EARLIER('Table'[Date])),'Table'[Quantity])

    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!

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Here you go bossamus 

    Full measure which returns the same as your expected result column in your sample data.

     

    Running Total Measure = 
VAR varName = MAX('Table'[Name])
VAR varFruit = MAX('Table'[Fruit])
VAR varCurrentDate = MAX('Table'[Date])
VAR Result = 
    SUMX(
        FILTER(
            ALL('Table'),
            'Table'[Name] = varName
                && 'Table'[Fruit] = varFruit
                && 'Table'[Date] <= varCurrentDate
        ),
        'Table'[Quantity]
    )
RETURN
    Result

     

     

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

    Hi bossamus ,

     

    You may also create a column as below:

    Fruit running total = SUMX(FILTER('Table','Table'[Fruit]=EARLIER('Table'[Fruit])&&'Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]<=EARLIER('Table'[Date])),'Table'[Quantity])

    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!