cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Tamir Member
Member

Running Totals (Accumulative) and Running Average

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Running Totals (Accumulative) and Running Average

hi, @Tamir

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Running Totals (Accumulative) and Running Average

hi, @Tamir

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Tamir Member
Member

Re: Running Totals (Accumulative) and Running Average

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