Forum Discussion

aesalasaguero's avatar
aesalasaguero
Frequent Visitor
2 years ago
Solved

Running total in Dax Studio

Hi There, Could anybody held with this Dax Query. I Group Sales by month and created a raking, as following:

DEFINE
VAR Sales =
ADDCOLUMNS (
VALUES ( 'DIM PRODUCTO'[PRODUCTO]),
"@SalesMonth", CALCULATE([VENTAS EN UNIDADES], 'DIM TIEMPO'[AÑO-MES]="2024-03")
)
EVALUATE
ADDCOLUMNS (
Sales,
"Rank",
RANKX (
Sales,
[@SalesMonth]
)

)
ORDER BY [@SalesMonth] DESC
This is the result:

Is there any way to do a running total based on this result?

Thanks

DAX
Dax studio
running total
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi aesalasaguero 

    I noticed an error in the original query which I have corrected in my earlier post (a reference to SalesTable that should have been Sales).

     

    In order to break ties as was suggested, one method for :

     

    DEFINE
    VAR Sales =
        ADDCOLUMNS (
            VALUES ( 'DIM PRODUCTO'[PRODUCTO] ),
            "@SalesMonth",
                CALCULATE (
                    [VENTAS EN UNIDADES],
                    'DIM TIEMPO'[AÑO-MES] = "2024-03"
                )
        )

EVALUATE
ADDCOLUMNS (
    Sales,
    "Rank",
        RANKX (
            Sales,
            [@SalesMonth]
        ),
    "RunningTotal",
        SUMX (
            WINDOW (
                1,
                ABS,
                0,
                REL,
                Sales,
                ORDERBY ( [@SalesMonth], DESC, 'DIM PRODUCTO'[PRODUCTO], ASC )
            ),
            [@SalesMonth]
        )
)
ORDER BY [@SalesMonth] DESC

     

     

    If your DAX version doesn't have the WINDOW function, you could use:

     

    DEFINE
    VAR Sales =
        ADDCOLUMNS (
            VALUES ( 'DIM PRODUCTO'[PRODUCTO] ),
            "@SalesMonth",
                CALCULATE (
                    [VENTAS EN UNIDADES],
                    'DIM TIEMPO'[AÑO-MES] = "2024-03"
                )
        )

EVALUATE
ADDCOLUMNS (
    Sales,
    "Rank",
        RANKX (
            Sales,
            [@SalesMonth]
        ),
    "RunningTotal",
    	VAR CurrentSalesMonth = [@SalesMonth]
    	VAR CurrentProduct = 'DIM PRODUCTO'[PRODUCTO]
    	RETURN
	        SUMX (
	            FILTER (
					Sales,
					[@SalesMonth] > CurrentSalesMonth
					|| [@SalesMonth] = CurrentSalesMonth && 'DIM PRODUCTO'[PRODUCTO] <= CurrentProduct
					),
	            	[@SalesMonth]
	        )
)
ORDER BY [@SalesMonth] DESC

     

     

7 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    2 years ago

    Hi aesalasaguero 

    If you want to add a running total to the table returned, you could use the WINDOW function. For example:

     

    DEFINE
    VAR Sales =
        ADDCOLUMNS (
            VALUES ( 'DIM PRODUCTO'[PRODUCTO] ),
            "@SalesMonth",
                CALCULATE (
                    [VENTAS EN UNIDADES],
                    'DIM TIEMPO'[AÑO-MES] = "2024-03"
                )
        )

EVALUATE
ADDCOLUMNS (
    Sales,
    "Rank",
        RANKX (
            Sales,
            [@SalesMonth]
        ),
    "RunningTotal",
        SUMX (
            WINDOW (
                1,
                ABS,
                0,
                REL,
                Sales,
                ORDERBY ( [@SalesMonth], DESC, 'DIM PRODUCTO'[PRODUCTO], ASC )
            ),
            [@SalesMonth]
        )
)
ORDER BY [@SalesMonth] DESC

     

     

    Is this the sort of thing you were looking for?

     

    Regards

    • aesalasaguero's avatar
      aesalasaguero
      Frequent Visitor
      2 years ago

      Hi Owen, I tried your approach, but I get an error message:

       

      Failed to resolve the name 'ABS'. It is not a valid table, variable or function name.

       

      what do you think could be the issue?

       

      Thanks

       

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    The standard pattern is to sum up all values that are greater than or equal to the current value (like a Pareto).  For ties you need to find a suitable tie breaker, for example the product name.

    • aesalasaguero's avatar
      aesalasaguero
      Frequent Visitor
      2 years ago

      Hi, I tried to do it, but I'm kind of lost on this. I added an extra column in ADDCOLUMS like this:

       "Sum",
                  CALCULATE(
                      sumx(
                          sales,
                          [@SalesMonth]
                      ),
                      FILTER(
                          ALLSELECTED(sales),
                          [@SalesMonth] >= MAX([@SalesMonth])
                      )
                  )

      but how do I put the suitable tie breaker here?

       

      Thanks

       

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    2 years ago

    Hi aesalasaguero 

    I noticed an error in the original query which I have corrected in my earlier post (a reference to SalesTable that should have been Sales).

     

    In order to break ties as was suggested, one method for :

     

    DEFINE
    VAR Sales =
        ADDCOLUMNS (
            VALUES ( 'DIM PRODUCTO'[PRODUCTO] ),
            "@SalesMonth",
                CALCULATE (
                    [VENTAS EN UNIDADES],
                    'DIM TIEMPO'[AÑO-MES] = "2024-03"
                )
        )

EVALUATE
ADDCOLUMNS (
    Sales,
    "Rank",
        RANKX (
            Sales,
            [@SalesMonth]
        ),
    "RunningTotal",
        SUMX (
            WINDOW (
                1,
                ABS,
                0,
                REL,
                Sales,
                ORDERBY ( [@SalesMonth], DESC, 'DIM PRODUCTO'[PRODUCTO], ASC )
            ),
            [@SalesMonth]
        )
)
ORDER BY [@SalesMonth] DESC

     

     

    If your DAX version doesn't have the WINDOW function, you could use:

     

    DEFINE
    VAR Sales =
        ADDCOLUMNS (
            VALUES ( 'DIM PRODUCTO'[PRODUCTO] ),
            "@SalesMonth",
                CALCULATE (
                    [VENTAS EN UNIDADES],
                    'DIM TIEMPO'[AÑO-MES] = "2024-03"
                )
        )

EVALUATE
ADDCOLUMNS (
    Sales,
    "Rank",
        RANKX (
            Sales,
            [@SalesMonth]
        ),
    "RunningTotal",
    	VAR CurrentSalesMonth = [@SalesMonth]
    	VAR CurrentProduct = 'DIM PRODUCTO'[PRODUCTO]
    	RETURN
	        SUMX (
	            FILTER (
					Sales,
					[@SalesMonth] > CurrentSalesMonth
					|| [@SalesMonth] = CurrentSalesMonth && 'DIM PRODUCTO'[PRODUCTO] <= CurrentProduct
					),
	            	[@SalesMonth]
	        )
)
ORDER BY [@SalesMonth] DESC