SUMX on a CALCULATETABLE - Filters not working as expected

[echristoph]

I've been trying this for several days in different configurations: CALCULATE, SUMX, using EARLIER/EARLIEST, with and without variables, ALL and without ALL, but nothing works. I've searched dozens of forum answers and YouTube videos. It seems so simple; maybe someone here can tell me what I missed:

The CA Running Totals should be :

• 1272.40
• 2030.5
• 1584.3
• 1576.3
• 1636.6

...but they are not, and I am sad. Any help is greatly appreciated!!

[echristoph · Accepted solution]

Solution was silly: had to set the CA Running Total field to Don't Summarize. Sorry for the bother!

[echristoph]

If it helps here's a snapshot of part of the source data:

What I need for each row is the total of all values with the same Control Account, Cost Set, Results, and a date <= to the Date.

[ValtteriN]

Hi,

Is there a reason why common RT patterns like (measure):

Var _date = MAX('Calendar'[Date]) return
CALCULATE(SUM(Cap[Value]),ALL('Calendar'),'Calendar'[Date]<_date)

For column:

RT =

CALCULATE(Sum(Cumulativetotal[Value]),

ALL('Cumulativetotal'),Cumulativetotal[Date]<=EARLIER(Cumulativetotal[Date]))

Don't work here?

[echristoph]

Tried it that way just in case I missed something; no luck:

[echristoph]

The Cost Set, Control Account, and Results columns all have multiple values that must be filtered before summing the total value for that row.

[goncalogeraldes]

Hello there @echristoph ! Can you check if this works for you?

CA Running Total =
VAR CurDate = [Date]
RETURN
    CALCULATE (
        SUMX (
            FILTER (
                SUMMARIZE (
                    ALL ( CAP ),
                    CAP[Control Account],
                    CAP[Cost Set],
                    CAP[Results],
                    CAP[Date],
                    "Sum", SUM ( [Your_sum_field] )
                ),
                CAP[Date] <= CurDate
            ),
            [Sum]
        )
    )

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[echristoph]

Looked promising, but no luck: