Forum Discussion

mfattibello's avatar
mfattibello
Regular Visitor
8 years ago

Problems on implementing RunningSum measure using DAX

Dear,

 

 

I´m facing problems on implementing RunningSum measure using DAX. I have a rank measure showing the best revenues on a determined period and another dimension´s elements, according with my slicers. I´m trying to create a revenue running sum measure based on this rank, so the revenue would be cummulative until the last value of this rank. I cannot create the rank logic as a column, because my table has hundreds and hundreds of rows. Below, a schema about what I need to do.

Can anyone help me, please?

 

RKRevenueRunningSum
1900000900000
28000001700000
37000002400000
46000003000000
55000003500000
64000003900000


  

Best Regards,

Marcos Fattibello

Need Help
Tutorial Requests

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi mfattibello,

     

    I'm assuming you have another column on your table that identifies the ranking and not only based on the value.

     

    Try this measure and see if it works:

    CUMULATIVE =
VAR Sum_Revenue =
    SUM ( Ranking[Revenue] )
RETURN
    IF (
        NOT ( ISBLANK ( Sum_Revenue ) ),
        CALCULATE ( Sum_Revenue ),
        FILTER ( ALL ( Ranking[TYPE] ), SUM ( Ranking[Revenue] ) <= Sum_Revenue )
    )

    As you can see in my formula I assumed you have a TYPE column in wich you make the ranking work.

     

     

    In this table the Ranking is made using the revenue column 

    Ranking = RANKX(ALL(Ranking[Revenue]),CALCULATE(SUM(Ranking[Revenue])))

    However to make the cumulative total I have to use another column (TYPE).

     

    Regards,

    MFelix

     

     

    • mfattibello's avatar
      mfattibello
      Regular Visitor
      8 years ago
      Hello, MFelix and v-yulgu-msft!

       

      Thanks for yours explanations. On my scenario, I don´t have a specific column at my table to ranking be identified. The user chooses some options at the slicer. So, according with these selections, my ranking measure is calculated based on the revenue values. 
      Somehow, my ranking measure is a little "crazy". As you can see at the image, the position '2' dissapeared. The measure with red borders is my revenue (divided per 1000), and the Running Sum calculation is doing correctly until the RK '5', where the calculation gets wrong.

      BTW, I had to transcript the results into a Excel grid, because I cannot paste the image here :(

       

      RKRunning SumRevenue
      1316,79316,8
      3457,83141
      4497,940,1
      5212,7731,7
      6559,5630
      7251,799
      8258,586,8

       


      Best regards,

      Marcos Fattibello

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        Could you share the link from where i can download your workbook.  Please remove information which is not relevnt for the question.

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

    Hi mfattibello,

     

    Below is my test.

     

    Sample dataset.

     

    Create measures.

    Ranking =
RANKX (
    ALLSELECTED ( Ranking[Category] ),
    CALCULATE ( SUM ( Ranking[Revenue] ) ),
    ,
    ASC,
    DENSE
)

Running sum =
VAR rankvalue = Ranking[Ranking]
RETURN
    CALCULATE (
        SUM ( Ranking[Revenue] ),
        FILTER ( ALLSELECTED ( Ranking[Category] ), Ranking[Ranking] <= rankvalue )
    )

    Result.

     

    Best regards,
    Yuliana Gu