topic Cumulative total and order in DAX Commands and Tips

Cumulative total and order

lebroyeur — Mon, 18 Jan 2021 00:23:29 GMT

Hello everyone,

I am new to powerBI and I'm struggling to create a running total.

I only have one table, that looks like this :

SALES

MARKET A 100

MARKET A 50

MARKET B 30

MARKET C 600

MARKET C 400

MARKET D 700

What I need is a running total that would be ordered by number of sales per market (going from larger to smaller market), like this :

SALES. RUN-TOTAL

MARKET A 150 1850

MARKET B 30 1880

MARKET C 1000 1000

MARKET D 700 1700

Using quick measures, I have only been able to create running total ordered by market name, like this :

SALES. RUN-TOTAL

MARKET A 150 150

MARKET B 30 180

MARKET C 1000 1180

MARKET D 700 1880

I have also tried some things in DAX, but I am not familiar enough with it yet.

Thank you in advance !

Re: Cumulative total and order

mahoneypat — Mon, 18 Jan 2021 00:40:31 GMT

Here is a measure expression that gets your desired result.

RT Market =
VAR vThisMarket =
    MIN ( Sales2[Market] )
VAR vSummary =
    ADDCOLUMNS (
        ALLSELECTED ( Sales2[Market] ),
        "cSales",
            CALCULATE (
                SUM ( Sales2[Sales] )
            )
    )
VAR vThisSales =
    SUMX (
        FILTER (
            vSummary,
            Sales2[Market] = vThisMarket
        ),
        [cSales]
    )
RETURN
    SUMX (
        FILTER (
            vSummary,
            [cSales] >= vThisSales
        ),
        [cSales]
    )

Pat

Re: Cumulative total and order

wdx223_Daniel — Mon, 18 Jan 2021 01:36:41 GMT

lebroyeur