Forum Discussion

CupidC's avatar
CupidC
Helper II
2 years ago
Solved

Cumulative Column in a Query Model with Virtual Tables

Hello everyone,

I'm seeking advice on how to generate a cumulative column within the query model, - dealing with a virtual rather than a physical table.

 

For instance, I encounter an error when attempting to use the following formula. I've marked my desired outcome in red in the attached example, but I'm uncertain about the correct approach to achieve this result. Any guidance would be greatly appreciated.

 

 

Need Help
  • CupidC's avatar
    CupidC
    2 years ago

    Hi Amitchandak,

     

    Thank you! It's working perfectly. I have another question: is there a possibility to use a variable instead of using  Earlier? I've come across several posts recommending against this function and was wondering if there's an alternative method involving variables or something else..  thanks!

     

    Thanks

    Cupid

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi CupidC ,
    You can try this

    DemoTable = 
VAR vTable =
    DATATABLE (
        "Index",INTEGER,
        "Name", STRING,
        "Value", INTEGER,
        {
            { 1,"A", 1 },
            { 2,"A", 2 },
            { 3,"A", 3 }
        }
    )
VAR vAddRT =
    ADDCOLUMNS (
        vTable,
        "RT",
            VAR thisrowindex = [Index]
            RETURN
                SUMX ( FILTER ( vTable, [Index] <= thisrowindex ), [Value] )
    )
RETURN
    vAddRT

    Final output

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    CupidC ,

    Try a new column

    Sumx(filter(Table, [Value] <= Earlier([Value]) ), [Value])

    • CupidC's avatar
      CupidC
      Helper II
      2 years ago

      Hi Amitchandak,

       

      Thank you! It's working perfectly. I have another question: is there a possibility to use a variable instead of using  Earlier? I've come across several posts recommending against this function and was wondering if there's an alternative method involving variables or something else..  thanks!

       

      Thanks

      Cupid

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi CupidC ,
    Here is a dax expression that does not use the earlier function:

    Accumulation = 
CALCULATE(
    SUM('Table'[VALUE]),
    FILTER(
        ALLEXCEPT('Table','Table'[ID]),
        'Table'[VALUE] <= MAX('Table'[VALUE])
        )
    )

    Final output

    For more information about the operation of accumulation you can refer to this blog
    How to Calculate Cumulative Values with DAX - Microsoft Fabric Community

     

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • CupidC's avatar
      CupidC
      Helper II
      2 years ago

      Hi Albet

       

      Thanks,, but the table is created virtually, so i can't reference "Table" in query mode, do you have other ways to achieve the result..?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi CupidC ,
        You can try this

        DemoTable = 
VAR vTable =
    DATATABLE (
        "Index",INTEGER,
        "Name", STRING,
        "Value", INTEGER,
        {
            { 1,"A", 1 },
            { 2,"A", 2 },
            { 3,"A", 3 }
        }
    )
VAR vAddRT =
    ADDCOLUMNS (
        vTable,
        "RT",
            VAR thisrowindex = [Index]
            RETURN
                SUMX ( FILTER ( vTable, [Index] <= thisrowindex ), [Value] )
    )
RETURN
    vAddRT

        Final output

        Best regards,

        Albert He

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly