Forum Discussion
Running total for 'row count'
Hi,
I'm looking for a variant of the often mentioned running total calculation.
My data does not contain the numbers to add up in the running total, like most Sales data examples around.
My data IS the number to add up, each row being a ticket at a Service Desk.
I would like to know the running total for tickets created and/or closed, by Date:
| Source data | ||
| Nr | Creation | Closure |
| INC0001 | 1/01/2019 | 1/05/2019 |
| INC0002 | 1/01/2019 | 1/06/2019 |
| INC0003 | 1/02/2019 | 1/06/2019 |
| REQ0001 | 1/01/2019 | 1/07/2019 |
| REQ0002 | 1/02/2019 | 1/07/2019 |
| REQ0003 | 1/02/2019 | 1/08/2019 |
Filtering 'INC*' from 'REQ*' can be done on a visual level, so I don't think that needs to be in the code(?)
So I would like to end up with these results (not necessarily combined in 1 measure/visual/...):
| Incidents opened | Incidents closed | |||
| 01/2019 | 2 | 05/2019 | 1 | |
| 02/2019 | 3 | 06/2019 | 3 | |
| Requests opened | Requests closed | |||
| 01/2019 | 1 | 07/2019 | 2 | |
| 02/2019 | 3 | 08/2019 | 3 |
Thanks for the help!
Hi BartVanH,
You could refer to modifying your formula as below:
Measure running total in value = SUMX(FILTER(ALLEXCEPT(Table1,Table1[Attribute],Table1[Legend],Table1[Nr]),'Table1'[Value]<=MAX('Table1'[Value])),[Measure])Result:
Regards,
Daniel He
4 Replies
Hi BartVanH,
Based on my test, you could refer to below steps:
Unpivot your Creation and Closure column in query editor:
Result:
Apply it and create below measures:
Measure = CALCULATE(COUNT(Table1[Value]))
Measure running total in value = SUMX(FILTER(ALLSELECTED(Table1[Value]),'Table1'[Value]<=MAX('Table1'[Value])),[Measure])Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi v-danhe-msft,
Thanks a lot!
The calculation seems to work perfectly!
Here it is in a line graph:
However, when I select the Date Hierarchy in the field well:
It all reverts back to the individual values once I start drilling down...
I have a feeling I know why it's doing that, but is there any way around?
The first version is not my favorite, it's not the most aesthetic way to display the data.
But if there is no way around, I'll use that.
Thanks again!
Bart
Hi BartVanH,
You could refer to modifying your formula as below:
Measure running total in value = SUMX(FILTER(ALLEXCEPT(Table1,Table1[Attribute],Table1[Legend],Table1[Nr]),'Table1'[Value]<=MAX('Table1'[Value])),[Measure])Result:
Regards,
Daniel He