Cumulative total and order

[lebroyeur]

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 !

[wdx223_Daniel · Accepted solution]

@lebroyeur 

[mahoneypat]

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