Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-lili6-msft
Community Support
Community Support

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

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.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

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

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.
Anonymous
Not applicable

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

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.