Forum Discussion

gumis_rulez's avatar
gumis_rulez
Icon for Helper I rankHelper I
2 years ago
Solved

Properly calculating cummulative value

I am struggling with cummulative value when I am creating a summarize table in dax measure:   I am using "others" to calculate the result for all other customers which have rank higher than p...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from FlipFlop1 and Selva-Salimi.

     

    Hi gumis_rulez  , 

     

    Here is the formula I provided:

     

    MEASURE =
    VAR _table =
    ADDCOLUMNS (
    SUMMARIZE ( ALL ( 'hlpCustomer' ), 'hlpCustomer'[customer_name] ),
    "rank", [Rank]
    )
    VAR _current_rank = [Rank]
    RETURN
    IF (
    ISINSCOPE ( hlpCustomer[customer_name] ),
    SUMX ( FILTER ( _table, [rank] <= _current_rank ), [Revenue] )
    )

     

    The result is as follows:

    Best Regards,
    Zhu
    Community Support Team

     

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

FlipFlop1's avatar
FlipFlop1
Icon for Advocate I rankAdvocate I
2 years ago

So you can use a quick measure to generate the cumulative total for you: See Quick Measure > Running total.

I think the DAX will look like this for your example:


Sales running total in Customer_name =
CALCULATE(
    [Revenue],
    FILTER(
        ALLSELECTED(),
        ISONORAFTER('hlpCustomer'[customer_name], MAX('hlpCustomer'[customer_name]),ASC)
    )
)