Running total in Dax Studio

[aesalasaguero]

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

[OwenAuger · Accepted solution]

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

[aesalasaguero]

Hi Owen, 

The second solution gets error timeout, but I deleted the line: 

|| [@SalesMonth] = CurrentSalesMonth && 'DIM PRODUCTO'[PRODUCTO] <= CurrentProduct

 and It worked perfectly.

Thank you very much.

Just for general knowledge. the WINDOWS function is not included in my DAX, is it a possible solution to update my SSAS version to a newer one?

[OwenAuger]

Glad to hear it 🙂

I don't have specific experience on upgrading SSAS Tabular, but see here:

https://learn.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-analysis-services?view...

It appears that you need a recent build of SSAS 2022 for WINDOW and related functions.

https://dax.guide/WINDOW/

Regards

[lbendlin]

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]

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]

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]

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