Cumulative sum of a column by rows and should be filtered by dimensions

[Ram_DCT]

Hi Team,

I am looking for a cumulative sum of a column in my dataset using calcuated COLUMN only as I need to use that column in Table Visual for a specific use. Below is the sample data where I have given on how I want my output.

Original Data With Cumulative sum in column named as cumulative [I am getting this cumulative correct when I am not filtering my data with any dimensions I have]

ReportID	Report Name	Count	Cumulative	Category	Frequency
1001	Report 1	1	1	Category1	D
1002	Report 3	1	2	Category1	M
1003	Report 5	1	3	Category1	D
1004	Report 7	1	4	Category2	M
1005	Report 9	1	5	Category2	W
1006	Report 11	1	6	Category2	D
1007	Report 13	1	7	Category3	M
1008	Report 15	1	8	Category3	M
1009	Report 17	1	9	Category3	D
1010	Report 19	1	10	Category3	M
1011	Report 21	1	11	Category3	M
1012	Report 23	1	12	Category4	D
1013	Report 25	1	13	Category4	D

What I need is whenever I filter my above table - my cumulative sum should start from that point - for example - If filter category 4 and Frequency D - below is should be the expected result with culculated column

ReportID	Report Name	Count	Cumulative	Category	Frequency
1012	Report 23	1	1	Category4	D
1013	Report 25	1	2	Category4	D

I am getting my result correct using a measure but not using a calcuated column when I aplpy filters on the dataset I have.

Appreciate your quick help here!!

Thanks,

Ram

[V-pazhen-msft]

@Ram_DCT 

Unfortunately, calculate column cannot be dynamic. and measure is dynamically changing based on interactions. That is one of the basically differences between them.

Best Regards
Paul Zheng _ Community Support Team

[Fowmy]

@Ram_DCT 

That is correct, when you create a calculated column, that gets added to your table and it is not recalculated when you use them on the reports with slicers and filter. The measures on the other hand will react to filter context and perform calculations based on the current context.

 

[Ram_DCT]

However, I need to have a calculated column which will behave just like a measure when sliced and diced.

-Ram

[Fowmy]

@Ram_DCT 

Unfortunately, it is not possible. You can create a measure to achieve this.

[Ram_DCT]

Can I use RANKX to get dynamic ranking column besed on the cumulative sum of the caluclated column?

[Fowmy]

@Ram_DCT 

You can create this measure:

Rank = 
RANKX( ALLSELECTED(Table) , CALCULATE(SUM(Table[Cumulative])),,ASC) 

[Ram_DCT]

I am so sorry to be rigid here but I need to have column only. Any other option to achieve this?