How to calculate cumulative total category wise with blanks in values

[balajir98]

Region	Plan_Date	Target	Cummulative Total
East	01/05/2021	0
East	02/05/2021	0
East	03/05/2021	20
East	04/05/2021	25
East	05/05/2021	30
East	06/05/2021	35
East	07/05/2021	40
East	08/05/2021	0
East	09/05/2021	0
East	10/05/2021	55
East	11/05/2021	60
West	01/05/2021	0
West	02/05/2021	0
West	03/05/2021	75
West	04/05/2021	80
West	05/05/2021	85
West	06/05/2021	90
West	07/05/2021	95
West	08/05/2021	0
West	09/05/2021	0
West	10/05/2021	110
West	11/05/2021	115

Expected Output :-

Region	Plan_Date	Target	Cummulative Total
East	01/05/2021	0	0
East	02/05/2021	0	0
East	03/05/2021	20	20
East	04/05/2021	25	45
East	05/05/2021	30	75
East	06/05/2021	35	110
East	07/05/2021	40	150
East	08/05/2021	0	150
East	09/05/2021	0	150
East	10/05/2021	55	205
East	11/05/2021	60	265
West	01/05/2021	0	0
West	02/05/2021	0	0
West	03/05/2021	75	75
West	04/05/2021	80	155
West	05/05/2021	85	240
West	06/05/2021	90	330
West	07/05/2021	95	425
West	08/05/2021	0	425
West	09/05/2021	0	425
West	10/05/2021	110	535
West	11/05/2021	115	650
North	01/05/2021	0	0
North	02/05/2021	0	0
North	03/05/2021	130	130
North	04/05/2021	135	265
North	05/05/2021	140	405
North	06/05/2021	145	550
North	07/05/2021	150	700
North	08/05/2021	0	700
North	09/05/2021	0	700
North	10/05/2021	165	865
North	11/05/2021	170	1035
South	01/05/2021	0	0
South	02/05/2021	0	0
South	03/05/2021	185	185
South	04/05/2021	190	375
South	05/05/2021	195	570
South	06/05/2021	200	770
South	07/05/2021	205	975
South	08/05/2021	0	975
South	09/05/2021	0	975
South	10/05/2021	220	1195
South	11/05/2021	225	1420

[FrankAT]

Hi @balajir98 ,

here is my solution:

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]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@balajir98 , Try

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

[balajir98]

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

[aj1973]

Hi @balajir98 

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.

[balajir98]

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]

@balajir98 

Change Sheet1 by the name of your table

[balajir98]

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]

@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

[balajir98]

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

[aj1973]

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

[balajir98]

Ok jerbi, understood, i have in table also, though it is not workings, please find the below screenshot

[aj1973 · Accepted solution]

@balajir98 

ok try this 

RunningTotal =

CALCULATE (

SUM ( Sheet1[Target] ),

Sheet1[Plan_Date] <= EARLIER ( Sheet1[Plan_Date]),

ALLEXCEPT ( Sheet1,Sheet1[Region] )

)

[balajir98]

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

[aj1973]

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