March 19, 2023
I want to add a RunningTotal column to my data using Pivot table
i've the following data:
So i insert my data in a pivot table like the following:
It's a very simple pivot table with the "id" field in the rows section, and the "item" in the filters section, and the "SalesAmount" in the values section,
i set the "Show Value As" of the SalesAmount column "Value Filed settings" to "Runing Total in" and set the Base Field to id and it works fine,
but i also want to show the customer, so i add this field to the Rows section after the "id" Field but as soon as i do this the running total column becomes distorted, as if the summing process reset when the customer change, or Excel auto group the data according to the customer despite that i set the Base Field of the running total column to id.
What i really which is that excel calculate the running total across all the rows that are visible in the pivot table.
July 16, 2010
You can use this DAX measure:
=CALCULATE(SUM([SalesAmount]),Table1[id] <= MAX(Table1[id]), ALL(Table1[Customer]))
If you'd like to learn more about writing this kind of DAX measure, please consider my Power Pivot and DAX course.