Running Totals (Accumulative) and Running Average

[Anonymous]

Hi,

I need help with getting the measures right for an Accumulative score and a running Average score.

This formula works if I do not filter:

Running Total Score = 
CALCULATE(
    SUM(data[Score]),
    FILTER(
        ALL(data[Period]),
        data[Period]<=MAX(data[Period])
    ))

 I tried this one:

Running Totals w SUMX = 
CALCULATE(
    SUMX(VALUES(data[Period]),[Total Score]),
            FILTER(
                ALL(data),
                data[Period]<=MAX(data[Period]))
        )

But it is also not working when I filter.

Please see linked file

Thank you, Tamir

[v-lili6-msft · Accepted solution]

hi, @Anonymous

Whether this formula that fits your needs

Running Total Score = 
CALCULATE(
 SUM(data[Score]),
 FILTER(
 ALLSELECTED(data[Period]),
 data[Period]<=MAX(data[Period])
 ))

If not your case, please show us your expected output after you filter it.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft

Thank you for your solution, I believe it to work.

However, I finally came up with this formula:

Overall Performance YTD = 
CALCULATE(
    SUMX(VALUES(dimCalendar[MonthNum]),[Overall Performance]),
    FILTER(
        ALLSELECTED(dimCalendar[MonthNum]),
        dimCalendar[MonthNum]<=MAX(dimCalendar[MonthNum]))
)

It works fine.

However, how can I use the above format if the leading column is a string and not numeric?

The MAX function will not be usable...

One use would be if my column is a named month rather then a number...

Or a name of a category...

Thank you,

Tamir