Forum Discussion

tezeife's avatar
tezeife
Frequent Visitor
9 years ago
Solved

Running totals

I'm trying to do a cumulative total on a table with no distinct column, Is this possible to include a DISTINCT function in the daxCumm when creating a Rank? This way i can then do a running total based on the rank? please help

  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi tezeife,

     

    Suppose this is your expected result of Running total.

     

    To achieve that, you need two calculated columns:

    Average =
'Running Total'[Value]
    / CALCULATE (
        COUNT ( 'Running Total'[Rank] ),
        ALLEXCEPT ( 'Running Total', 'Running Total'[Value] )
    )

Running value =
CALCULATE (
    SUM ( 'Running Total'[Average] ),
    FILTER (
        'Running Total',
        'Running Total'[Rank] <= EARLIER ( 'Running Total'[Rank] )
    )
)

    Best regards,
    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi tezeife,

     

    Suppose this is your expected result of Running total.

     

    To achieve that, you need two calculated columns:

    Average =
'Running Total'[Value]
    / CALCULATE (
        COUNT ( 'Running Total'[Rank] ),
        ALLEXCEPT ( 'Running Total', 'Running Total'[Value] )
    )

Running value =
CALCULATE (
    SUM ( 'Running Total'[Average] ),
    FILTER (
        'Running Total',
        'Running Total'[Rank] <= EARLIER ( 'Running Total'[Rank] )
    )
)

    Best regards,
    Yuliana Gu