Forum Discussion
running total in pivot table when grouping dates
in my file there are sums in the context of dates. I formed a pivot table where in the rows of the date, in the columns of the sum. I need to output the running total. for this, the field was re-displ...
With grouping the date built-in Running Total resets calculations from the beginning of each group. To calculate running total through all dates you need to add data to data model and create the measure to calculate it.
On such sample
Creating PivotTable add data to data model
add two measures
Sum Value:=SUM(Table1[Value])
and
Value Running Total:=VAR
MaxDate=MAX ( Table1[Date] )
RETURN
CALCULATE([Sum Value],
Table1[Date]<= MaxDate,
ALL (Table1)
)Instead of first one you may use implicit measure which sums value, but better to keep everything in hands and use explicit measures.
In second one MaxDate will be maximum date for the current group, removing all other filters from the Table we calculate sum of Value till MaxDate.
Result is
I am very curious how you would write the measures to do this with multiple categorical columns. In the above sample your row labels are the dates, but if you want a running total measure for each of two columns called "Sum of Value - GroupA" and "Sum of Value - GroupB".
I do not see grouping function within the calculate when you create the measure.
I do not see grouping function within the calculate when you create the measure.
Instead of removing all filters with ALL() you may remove all but Group filter with ALLEXCEPT()
RTotal:=VAR MaxDate=MAX ( Table1[Date] ) RETURN CALCULATE( [Total], Table1[Date]<= MaxDate, ALLEXCEPT( Table1, Table1[Group] ) )