cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WNA247365
Frequent Visitor

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.

 

The data looks like this (there are 7 days of data as the columns so there would be days for 6/28, 6/29, 6/30.....

Date Received           2016-06-28

Delivered                   Total Samples

-------------------------------------------

Before 12         |          7

Before 3           |      122

Before 5           |       192

Before 7           |        807

 

I'm trying to make another column for a running total (actually I'll be making a running percentage for the day but I'm trying to just get the simple runnng total to work first), the final result set would be something like this:

 

Date Received           2016-06-28

Delivered                   Total Samples       Running Total      Running Percentage

---------------------------------------------------------------------------------------

Before 12         |          7                                  7                           0.62%

Before 3           |      122                               129                         11.44%

Before 5           |       192                              321                         28.46%

Before 7           |        807                           1128                        100.00%

 

If I change the row value to be a number I can get this to work with a formula like the following:

 

Running Total = CALCULATE(Sum('qrySpecimenTracking\'[Total Samples]),FILTER(ALL('qrySpecimenTracking\'[DeliveredSort]),'qrySpecimenTracking\'[DeliveredSort] <= Max('qrySpecimenTracking\'[DeliveredSort])))

 

But the actual column that I need for the row values isn't a numeric so won't evaluate in a DAX <= expression.

 

Thanks in advance for any help / suggestions.

1 ACCEPTED SOLUTION
jahida
Impactful Individual
Impactful Individual

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:

 

Capture.PNG

 

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.

 

View solution in original post

5 REPLIES 5
samdthompson
Solution Sage
Solution Sage

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().

// if this is a solution please mark as such. Kudos always appreciated.

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.

jahida
Impactful Individual
Impactful Individual

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
Frequent Visitor

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
Impactful Individual
Impactful Individual

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:

 

Capture.PNG

 

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.

 

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors