Running Total without any date filters in DAX

0
votes

I am trying to get a running total calculation for a set of data that does not have any Dates. I have a column with Product Categories and a column with Sales Dollars for each Product Category from the Fact Table. I need to add a running total column as shown below. I am new to DAX and looking for some help on calculating a running total.

Category                Sales $  Cumulative Sales $
FOOD SERVICE            9051        9051
HOT FOOD                1880       10931
GRILL                   1815       12746
FRESH SANDWICHES        1189       13935
FRESH BAKERY            1100       15035
PACKAGED BAKERY         1074       16109
COLD SNACKS              645       16754
FAST FOOD                388       17142
FRESH BAKERY MULTI-DAY   252       17394
ENTREES/SALAD            180       17574
NACHOS                   126       17700
BREAD                    120       17820
Grand Total            17820
1
dax

1 Answers

0
votes

I think you have to have it ordered by something. If you want it ordered by Sales as you have it above you could create a calculated column to store the order as follows:

=    RANKX ( ALL ( Table1 ), [Sales], [Sales] )

Then create a new calculated column for your cummulative total:

=
CALCULATE (
    SUM ( [Sales] ),
    FILTER (
        ALL ( Table1 ),
        [CalculatedColumn2] <= EARLIER ( Table1[CalculatedColumn2] )
    )
)