Running Total in DAX Analysis Services issue

[sqlguru448]

Hello,

Can you please help me in the below DAX which I am trying to write in creating Running Total based off Index. I am trying to execute in Analysis services not in Power BI.

EVALUATE
 (
    VAR countsales =
        SUMMARIZECOLUMNS (
            'DimProductSubcategory'[EnglishProductSubcategoryName],
            "counter", SUMX (
                FactInternetSales,
                FactInternetSales[OrderQuantity] * FactInternetSales[UnitPrice]
            )
        )
    VAR FINAL =
        ADDCOLUMNS (
            countsales,
            "Indexz", RANKX ( countsales, DimProductSubcategory[EnglishProductSubcategoryName],, asc )
        ) //var trying = ADDCOLUMNS(FINAL, "Running Total", CALCULATE(SUMX(FINAL,[counter]), FILTER(ALL(final),[Indexz]<=[Indexz])))
    RETURN
        ADDCOLUMNS (
            FINAL,
            "Running Total", CALCULATE (
                SUMX ( final, [counter] ),
                FILTER ( final, [Indexz] <= EARLIER ( [Indexz] ) )
            )
        )
)
ORDER BY DimProductSubcategory[EnglishProductSubcategoryName]

Please help/guide me to correct above DAX in creating a valid running total.

[v-lili6-msft · Accepted solution]

HI, @sqlguru448

After my test, you could try this formula as below:

EVALUATE
 (
    VAR countsales =
        SUMMARIZECOLUMNS (
            'DimProductSubcategory'[EnglishProductSubcategoryName],
            "counter", SUMX (
                FactInternetSales,
                FactInternetSales[OrderQuantity] * FactInternetSales[UnitPrice]
            )
        )
    VAR FINAL =
        ADDCOLUMNS (
            countsales,
            "Indexz", RANKX ( countsales, DimProductSubcategory[EnglishProductSubcategoryName],, asc )
        ) //var trying = ADDCOLUMNS(FINAL, "Running Total", CALCULATE(SUMX(FINAL,[counter]), FILTER(ALL(final),[Indexz]<=[Indexz])))
    RETURN
        ADDCOLUMNS (
            FINAL,
            "Running Total",
            VAR currentIndex = [Indexz]
            RETURN
                SUMX ( FILTER ( FINAL, [Indexz] <= currentIndex ), [counter] )
        )
)
ORDER BY DimProductSubcategory[EnglishProductSubcategoryName]

Result:

Best Regards,

Lin

[sqlguru448]

Hi Lin,

Can you please explain why my DAX was not working even though I was using EARLIER function? is this because of FILTER context being applied instead of row context?

Thank you very much for your quick response.

[sqlguru448]

Can someone please help?