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.

     

samdthompson's avatar
samdthompson
Memorable Member
10 years ago

You will find it quite hard to do any calculations without the data being a value. Can you go back to the query editor and change the data type to values or alternatively a calculated column to turn the data into values  with =VALUE().

WNA247365's avatar
WNA247365
Frequent Visitor
10 years ago

Hi samdthompson, 

Thank you for the reply.  I have done that, and as mentioned above if I change the value of "Delivered" to be 1,2,3,4 representing the text values then this works properly, the issue with that is that I then lose the meaning in the row values in the matrix.  If I have both the value representation and the text representation as row values then it doesn't create a running total.  It shows the number per the value for "Delivered".  It will only show the running total if I turn on Total row under the general properties.

 

I'm not sure that using Values will help, since that will return a number multiplied by the number of rows that made up the summary value and I always want the order to be as I have it in my example.

 

It seems strange that this is so difficult in DAX when it was very easy using QlikSense and even SSRS.

 

Thanks again for taking the time to reply and for the insight.