Running total as measure without date

[Anonymous]

Hello everybody,

I am struggling about calculating running total as measure without date column. Quick measure has a running total option but it needs a base column(generally it is date column) which is completely understandable.
In my case I need to calculate running sum on highest value to lowest in a table and this table can change of every click on slicers. So I need to calculate dynamic ranking and afterwards I need to sum up based on this ranking.
What I have found on web is mostly people use calculated columns but in my case I have to do it as measure since it is a direct query.

Any comment would be helpful
Thanks in advance

You can find sample .pbix file as below.
https://gofile.io/?c=tobppC

Screenshot is as below

[JarroVGIT · Accepted solution]

Alright then @Anonymous , that took me way longer than I like to admit 😛 However, I came up with a solution. The measure you are looking for is this:

RolingSum = 
VAR curTable = CALCULATETABLE(
    ADDCOLUMNS( 
    SUMMARIZE(Sheet1,
        Sheet1[Station], Sheet1[Reason], Sheet1[Machine], "SumEC", SUM([EventCount])), "secondaryRank", Sheet1[Station]&Sheet1[Reason]&Sheet1[Machine])        , ALLSELECTED(Sheet1))
VAR rankedTable = ADDCOLUMNS(CurTable, "RankedCol", 
            RANKX(curTable, 
                RANKX(curTable, [SumEC], ,ASC) + 
                DIVIDE(RANKX(curTable, [secondaryRank],, DESC), COUNTROWS(curTable) +1), , ASC))
VAR curStation = SELECTEDVALUE(Sheet1[Station])
var curMachine = SELECTEDVALUE(Sheet1[Machine])
VAR curReason = SELECTEDVALUE(Sheet1[Reason])
VAR curRank = SUMX(FILTER(rankedTable, [Machine] = curMachine && [Station]=curStation && [Reason] = curReason), [RankedCol])
RETURN
SUMX(FILTER(rankedTable, [RankedCol] >= curRank), [SumEC])

The logic is as follows: you are showing a table visual with SUMS of [EventCount] (and also sums of [rownumber] but that is irrelevant). The first thing we need in our Measure context is a summary table of the current filtered context of the Sheet1 table (e.g. excluding the current applied filters from the visual but include explicit filters like slicers). We do that with CALCULATETABLE with as filter ALLSELECTED(Sheet1). (fun fact: i don't think I have had the please of using CALCULATETABLE yet, very cool stuff) Note that we add a uniquecolumn to this table where I concatenate the three relevant columns. We need that later on!

When we have this summary table (which is pretty similar to your table visual, except for the RowNumbers and now including a 'secondaryRank' column) we add a RANKX to it and store the new table in variable rankedTable. The RANKX actually evaluates against two columns; first it looks at the rank of the SumEC column, but secondary it looks at the 'secondaryRank' column. The second ranking is divided by the totalrows of the table, resulting in decimal values (e.g. is the secondary ranking is 1, and we divide it by rows=100, then the outcome of that part is 0.01). The decimal value is added to the original ranking (of SumEC) and thus we break every tie there might be. (this is required because in your example row 3 and 4 are of the same value, 198).

Next up, we need to know what our current evaluation parameters are in the Table Visual (this measure is ending up in that table) and we use the SELECTEDVALUE() for that, so we can determine the current Rank of the (visual) Table row, all the while this rank is looked up in the variable table rankedTable. Normally I would use LOOKUPVALUE but that didn't work here as the column we want to lookup from is in a virtual table and cannot be referenced. Because I am only interested in one row and I know for sure that my filters in the SUMX statement will return only 1 row, I can use SUMX to determine the RANK value. 

Eventually, I add up all the SumEC of every row with a higher or equal rank to my current rank.  The results are like this:

Note row 3 and 4, the ROling sum still works due to the cool RANKX()It keeps working when using filters.

Well, that was a lot of fun! Thanks for sharing your PBIX, that really helped in solving this puzzle. 

Kind regards

Djerro123

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

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

@JarroVGIT Thanks!!! That worked like a charm

[FrankAT]

Hi,

use the following measure:

Running Total =
CALCULATE(
SUM(Sheet1[EventCount]);
FILTER(
ALLSELECTED(Sheet1);
Sheet1[EventCount] <= MAX(Sheet1[EventCount])
)
)

Regards FrankAT