Forum Discussion
Cumulative Running Total in Power BI Table
My dataset has the below values. Name is a normal field and amount % is a calculated measure value.
I need to create a calculated measure or a column to make the cumulative running total % as given in the Column 3.
How can we do it ?
Hi, Anonymous
According to your description,you could first create a index column in Power Query: transform data->add column->index column
Then create a measure by the following formula:
Total = CALCULATE ( SUM ( 'Table'[Amount%] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] <= MAX ( 'Table'[Index] ) ) )
OrCreate a measure based on the number(1,2,3,4) in [Name] column.
Total2 = VAR _index = MID ( MAX ( 'Table'[Name] ), 8, 10 ) RETURN SUMX ( FILTER ( ALL ( 'Table' ), MID ( 'Table'[Name], 8, 10 ) <= _index ), [Amount%] )The final output is shown below:
Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-yalanwu-msftCommunity Support
Hi, Anonymous
According to your description,you could first create a index column in Power Query: transform data->add column->index column
Then create a measure by the following formula:
Total = CALCULATE ( SUM ( 'Table'[Amount%] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] <= MAX ( 'Table'[Index] ) ) )
OrCreate a measure based on the number(1,2,3,4) in [Name] column.
Total2 = VAR _index = MID ( MAX ( 'Table'[Name] ), 8, 10 ) RETURN SUMX ( FILTER ( ALL ( 'Table' ), MID ( 'Table'[Name], 8, 10 ) <= _index ), [Amount%] )The final output is shown below:
Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- amitchandak
Super User
Anonymous , Please try a measure like
calculate(Sumx(Values(Table[Name]),[Amount %]), filter(allselected(Table), Table[Name] =max(Table[Name])))
Else you have to this only for numerator of you measure
calculate(Sum(Table[Amount]), filter(allselected(Table), Table[Name] =max(Table[Name])))
