DAX Running Total/Cumulative Sum for Dimension

[tasmia93]

Hi, 
 

I am trying to get the cumulative total of products on this combo chart. I need the line to display the total running sum of the products starting with the largest sales. I tried using the DAX for date running sums but I cannot get it to work for products. Any help would be appreciated!

[tasmia93 · Accepted solution]

Thank you all for responses. I was able to get it working with the following DAX

Running Total Sale2 = 

VAR thissubcat =
    MIN ( 'Product'[Product] )
VAR summary =
    ADDCOLUMNS ( ALLSELECTED ( Product[Product] ), "cTS", [Total Sales] )
VAR thisTS =
    SUMX ( FILTER ( summary, Product[Product] = thissubcat ), [cTS] )
VAR result =
    SUMX ( FILTER ( summary, [cTS] >= thisTS ), [cTS] )
RETURN
    result

[v-yohua-msft]

Hi, @tasmia93 

I create a sample table:

Then create a measure and try the following DAX expression:

Running Total Sale = 
VAR CurrentProduct = MAX('Table'[ProductKey])
RETURN
    CALCULATE(
        [Total Sales],
        FILTER(
            ALL('Table'),
            'Table'[Sales] >= CALCULATE(MAX('Table'[Sales]), 'Table'[ProductKey] = CurrentProduct)&&'Table'[ProductKey]<=MAX('Table'[ProductKey])
        )
    )

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Idrissshatila]

Hello @tasmia93 ,

try the following 

rank your products based on their total sales

ProductRank = 
RANKX(
    ALL('Product'),
    [Total Sales],
    , 
    DESC
)

Running Total Sales = 
VAR MaxRank = MAX('Product'[ProductRank])
RETURN
    CALCULATE(
        [Total Sales],
        FILTER(
            ALL('Product'),
            'Product'[ProductRank] <= MaxRank
        )
    )