Running Cumm total by 2 groups

[Anonymous]

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!

[nandukrishnavs · Accepted solution]

@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 🙂

[v-shex-msft]

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

[lbendlin]

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]

@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]

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

[Anonymous]

@lbendlin Thank you for your help!

Could you plz try to import my EXcel file in Power BI  (Link attached). It would be great if you can provide me a solution in the workbook. CommTotal column will be output.

https://drive.google.com/drive/folders/18zuD7a2rh4pUS5_4LsBGI8zeuS5xqyzF?usp=sharing

[harshnathani]

Hi @Anonymous ,

You can use this measure

RT = 

VAR LastVisibleDate =
   MAX ('Table'[Date] )
VAR FirstVisibleDate =
    MIN ( 'Table'[Date])
VAR LastDateofRegions =
CALCULATE(MAX ('Table'[Date] ),ALLEXCEPT('Table','Table'[Regions]))
    
VAR Result =
    IF (
        FirstVisibleDate <= LastDateofRegions,
        CALCULATE (
            SUM('Table'[Actual Target]),
            'Table'[Date]<= LastVisibleDate
        
        )
    )
RETURN
Result

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[nandukrishnavs · Accepted solution]

@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 🙂

[Anonymous]

@nandukrishnavs  Your solution is amazing. When I applied to my real data it didn't work properly. I know I missed something and or some data issue. My actual column is count rows...contains 1 1 1 1 as I attached screenshot. I am not able to find where could be my issue. Please help me.

[Anonymous]

@nandukrishnavs  Hi, I am trying to calculate %  YTD  Target using DIVIDE function. I am not getting the correct output. Did I miss something?

[nandukrishnavs · Accepted solution]

@sbhan4

Use Max de TestCummTotal.

See the attached pbix file in the previous publication.

[Anonymous]

@nandukrishnavs Amazing... it worked. Thank you for your help!