Forum Discussion

paulfink's avatar
paulfink
Post Patron
5 years ago

Cumulative Sum

Hi guys,

 

I need a new Cumulative Sum formula.

 

The current one i have works fine but now as i am adding more stuff into my table it breaks the measure.

Cumulative Sum = CALCULATE(SUM('Table1[Value]), FILTER(ALLSELECTED('Table1'), 'Table1'[Index] <= MAX('Table1'[Index])))

 

My table is set up Monthly, when i sort by date - ascending there are multiple dates that are the same so if i base it off the dates. The Cumulative would group those values together instead of add on one by one like a Cumulative should work.

 

Everything in my visual is from the same visual so when i add in something from other table, in my case i am adding the ID and Completed Date.

 

How can i add those two fields into my visual without it breaking my Cumulative?

 

I tried using RELATED to pull the data in but it comes back with in an error - "The column 'Projects[ProjectNumber]' either doesn't exist or doesn't have a relationship to any table available in the current context."

 

Even though there is a relationship set up, but it is not a direct relationship like Table1 -> Table2

It is Table1 -> Table3 -> Table2

 

(Table2 being the table i am trying to get the IDs and Dates from)

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    paulfink , related can travel only one step at a time

    Try a column in table 3 from table 2

    and then a new column in table 1 from table 2

    • paulfink's avatar
      paulfink
      Post Patron
      5 years ago

      It worked in Table3 from Table2 but i got the same error when adding the column in Table1 from Table3

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi paulfink,

        It seems like you want to use the current table index as a parameter to loop 'table 1' records, right?
        If this is the case, you not need to build relationships between these tables. I'd like to suggest you extract the current table index value and use it as a condition to filter on 'table 1' records to get a corresponding result.

        Cumulative 2 =
CALCULATE (
    SUM ( Table[Value] ),
    FILTER ( ALLSELECTED ( Table ), Table[Index] <= Table2[Index] )
)

Cumulative 3 =
CALCULATE (
    SUM ( Table[Value] ),
    FILTER ( ALLSELECTED ( Table ), Table[Index] <= Table3[Index] )
)

        Regards,

        Xiaoxin Sheng