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 a...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    CupidC ,

    Try a new column

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

  • 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

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