cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
balajir98
Frequent Visitor

How to calculate cumulative total category wise with blanks in values

RegionPlan_DateTargetCummulative Total
East01/05/20210 
East02/05/20210 
East03/05/202120 
East04/05/202125 
East05/05/202130 
East06/05/202135 
East07/05/202140 
East08/05/20210 
East09/05/20210 
East10/05/202155 
East11/05/202160 
West01/05/20210 
West02/05/20210 
West03/05/202175 
West04/05/202180 
West05/05/202185 
West06/05/202190 
West07/05/202195 
West08/05/20210 
West09/05/20210 
West10/05/2021110 
West11/05/2021115 

 

Expected Output :-

 

RegionPlan_DateTargetCummulative Total
East01/05/202100
East02/05/202100
East03/05/20212020
East04/05/20212545
East05/05/20213075
East06/05/202135110
East07/05/202140150
East08/05/20210150
East09/05/20210150
East10/05/202155205
East11/05/202160265
West01/05/202100
West02/05/202100
West03/05/20217575
West04/05/202180155
West05/05/202185240
West06/05/202190330
West07/05/202195425
West08/05/20210425
West09/05/20210425
West10/05/2021110535
West11/05/2021115650
North01/05/202100
North02/05/202100
North03/05/2021130130
North04/05/2021135265
North05/05/2021140405
North06/05/2021145550
North07/05/2021150700
North08/05/20210700
North09/05/20210700
North10/05/2021165865
North11/05/20211701035
South01/05/202100
South02/05/202100
South03/05/2021185185
South04/05/2021190375
South05/05/2021195570
South06/05/2021200770
South07/05/2021205975
South08/05/20210975
South09/05/20210975
South10/05/20212201195
South11/05/20212251420
1 ACCEPTED SOLUTION
aj1973
Community Champion
Community Champion

@balajir98 

ok try this 

 

RunningTotal =
CALCULATE (
SUM ( Sheet1[Target] ),
Sheet1[Plan_Date] <= EARLIER ( Sheet1[Plan_Date]),
ALLEXCEPT ( Sheet1,Sheet1[Region] )
)
 
aj1973_0-1620838474553.png

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

15 REPLIES 15
FrankAT
Super User II
Super User II

Hi @balajir98 ,

here is my solution:

 

12-05-_2021_16-51-51.png

 

Running Total = 
CALCULATE (
    SUM ( 'Table'[Target] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Region] ),
        'Table'[Plan_Date] <= MAX ( 'Table'[Plan_Date])
    )
)

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User IV
Super User IV

@balajir98 , Try

 

calculate(sum(Table[Target]), filter(Table, [Region] = earlier([Region]) && [Plan_Date] <= max(Table[Plan_Date])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thank you for the reply amit, but i am getting the total value for all the rows, please find the below photos,

 

Table Output.PNGFunction.PNGSC.png

aj1973
Community Champion
Community Champion

Hi @balajir98 

aj1973_0-1620744438902.png

here is the formula

Target running total in Region =
CALCULATE(
    SUM('Table'[Target]),
    FILTER(
        ALLSELECTED('Table'[Region]),
        ISONORAFTER('Table'[Region], MIN('Table'[Region]), ASC)
    )
)
but it is better to use it as a measure and not as a calculated column.....It's better to avoid adding calculated column espacially when you are not using them as slicers.
 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

balajir98
Frequent Visitor

Thank you jer bi, but this is not my expected output, cummulated we should add the earlier value with 0, i have attached the image for your reference, Advance thanks

 

aj1973_0-1620744438902.png

aj1973
Community Champion
Community Champion

@balajir98 

aj1973_0-1620768776574.png

 

Change Sheet1 by the name of your table

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

balajir98
Frequent Visitor

Thank you Jerbi, now East region came perfect, but for west region the cummulative totals are mismatch, West March 5th Vale is 75 but in cummulative total it is showing as 95 and the missmatch follows, Thank you in advance.🙂

aj1973
Community Champion
Community Champion

@balajir98 

Yes myy bad sorry, 

Just delete the "<" before _region

 

Target running total in Region =
var _region = SELECTEDVALUE(Sheet1[Region])
var _maxdate = MAX(Sheet1[Plan_Date])
var result =
CALCULATE(
SUM(Sheet1[Target]),
Sheet1[Plan_Date] <= _maxdate,
Sheet1[Region] = _region
)
Return
result
 
aj1973_0-1620827420082.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

balajir98
Frequent Visitor

Thank you jerbi, i tried with your code, but i am getting blank, please find the below screenshotOutput.PNG

aj1973
Community Champion
Community Champion

@balajir98 

Indeed, it's a measure for a Table visual. It doesn't work for an added column.

Why do you need to add a column to your model? it takes space and performance, and we add Columns in order to use it as a slicer when necessary.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

balajir98
Frequent Visitor

Ok jerbi, understood, i have in table also, though it is not workings, please find the below screenshotOutput 3.PNG

aj1973
Community Champion
Community Champion

@balajir98 

ok try this 

 

RunningTotal =
CALCULATE (
SUM ( Sheet1[Target] ),
Sheet1[Plan_Date] <= EARLIER ( Sheet1[Plan_Date]),
ALLEXCEPT ( Sheet1,Sheet1[Region] )
)
 
aj1973_0-1620838474553.png

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

balajir98
Frequent Visitor

Thank you so much jerbi, it is working now perfect, thank you again for your help, 😊😊😊🙏

aj1973
Community Champion
Community Champion

@balajir98 

You are welcome, eventhough i don't recommend adding new columns into your model espcially when you are not using it as a slicer.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors