Forum Discussion

sqlguru448's avatar
sqlguru448
Icon for Helper III rankHelper III
7 years ago
Solved

Running Total in DAX Analysis Services issue

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.

 

Need Help
Show and Tell
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

     

     

     

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      sqlguru448
      Icon for Helper III rankHelper III
      7 years ago

      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.