Forum Discussion

WNA247365's avatar
WNA247365
Frequent Visitor
10 years ago
Solved

Running (cumulative) total without a numeric comparison

Hi I'm trying to create a running (cumulative) total  on a set of data in a matrix table.  My rows value isn't numeric so I can't use the formula that I see in all the examples I've been able to find...
Need Help
  • jahida's avatar
    jahida
    10 years ago

    There is definitely a (number of) easy way to do this, unless I'm missing something big. Here's one that I've tried on my machine:

     

    I created an index column in addition to the Delivered column. You can name it whatever you want (the default name is Index and I'm lazy). Here's the formula I'm using for running total:

     

    Running Total = CALCULATE(SUM(Query1[Total Samples]), FILTER(ALL(Query1), Query1[Index] <= EARLIER(Query1[Index])))

     

    And here's the table it creates:

     

     

    Another formula using the method I mentioned before (had to add the mod because 12 < 3, didn't clue into that before):

     

    Running Total2 = CALCULATE(SUM(Query1[Total Samples]), FILTER(ALL(Query1), 
	MOD(VALUE(RIGHT(Query1[Delivered], LEN(Query1[Delivered]) - SEARCH(" ", Query1[Delivered]))), 12) <=
	MOD(VALUE(RIGHT(EARLIER(Query1[Delivered]), LEN(EARLIER(Query1[Delivered])) - SEARCH(" ", EARLIER(Query1[Delivered])))), 12)))

    That gives exactly the same result as above (on my end at least), the formula is definitely more convoluted but it saves you a column. Hope one of those suits your purposes.

     

jahida's avatar
jahida
Impactful Individual
10 years ago

If you're fundamentally against changing the table structure, maybe you can parse that column to get a numeric value from it every time you need it? Eg. VALUE(RIGHT([Delivered], LEN([Delivered]) - SEARCH(" ", [Delivered])))

WNA247365's avatar
WNA247365
Frequent Visitor
10 years ago

Hi jahida,

Thank you for the reply.  I'm not against changing the structure of the returned data but per my reply to samdthompson above that has some difficulties as well.  I don't think the Value function will help based on trying it (again see my previous post) but I will try some more variations of using it.

 

Thanks again for taking the time to reply and for your suggestions.  I'm glad to know I didn't miss something obvious, hopefully this will be a feature enhancement in an upcoming release.

  • jahida's avatar
    jahida
    Impactful Individual
    10 years ago

    There is definitely a (number of) easy way to do this, unless I'm missing something big. Here's one that I've tried on my machine:

     

    I created an index column in addition to the Delivered column. You can name it whatever you want (the default name is Index and I'm lazy). Here's the formula I'm using for running total:

     

    Running Total = CALCULATE(SUM(Query1[Total Samples]), FILTER(ALL(Query1), Query1[Index] <= EARLIER(Query1[Index])))

     

    And here's the table it creates:

     

     

    Another formula using the method I mentioned before (had to add the mod because 12 < 3, didn't clue into that before):

     

    Running Total2 = CALCULATE(SUM(Query1[Total Samples]), FILTER(ALL(Query1), 
	MOD(VALUE(RIGHT(Query1[Delivered], LEN(Query1[Delivered]) - SEARCH(" ", Query1[Delivered]))), 12) <=
	MOD(VALUE(RIGHT(EARLIER(Query1[Delivered]), LEN(EARLIER(Query1[Delivered])) - SEARCH(" ", EARLIER(Query1[Delivered])))), 12)))

    That gives exactly the same result as above (on my end at least), the formula is definitely more convoluted but it saves you a column. Hope one of those suits your purposes.