Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Cumm total by 2 groups

Hello,

I want to calculate running Cumm Total by Region and Month[DATE].  I have attached my Excel and pbix file. I have a ton of records, this just dummy data. The month should be in order like Jan, Feb, March... Can anyone help me with this?

Link for attached file

 

Thank you for your help!

 

 

 

Need Help
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    Anonymous 

     

    If you are looking for a calculated column, try the below DAX in the calculated field.

     

    Running Total column =
SUMX (
    FILTER (
        Sheet1,
        Sheet1[Regions] = EARLIER ( Sheet1[Regions] )
            && YEAR ( Sheet1[Date] ) = YEAR ( EARLIER ( Sheet1[Date] ) )
            && Sheet1[Date] <= EARLIER ( Sheet1[Date] )
    ),
    Sheet1[Actual Target]
)

     

    If you are looking for a Measure. Use below DAX

    Running Total Measure = TOTALYTD(SUM(Sheet1[Actual Target]),'Date'[Date])

    Refer to the attached pbix file.


    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

11 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    You need two things - one is a Calendar/Dates table, and then other thing is the cumulative measure.  Luckily the second one is provided to you by Power BI for free - check out the Quick Measures, a collection of ready made formulas. Your scenario is supported.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      lbendlin Thank you for your reply. Could you help me to do that in my power bi workbook which is attached in the post.  

      • lbendlin's avatar
        lbendlin
        Super User
        6 years ago

        Your Power BI workbook is missing the Calendar/Dates table.  The fact table has very few data rows. Please share a better version.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous,

    You can take a look at following measure formula that calculates the cumulative total based on the current group and date if it meets your requirement:

    cumulative total =
VAR currDate =
    MAX ( Sheet1[Date] )
RETURN
    CALCULATE (
        SUM ( Sheet1[Actual Target] ),
        FILTER (
            ALLSELECTED ( Sheet1 ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && [Date] <= currDate
        ),
        VALUES ( Sheet1[Regions] )
    )

    Regards,

    Xiaoxin Sheng