hi,
I want to add a RunningTotal column to my data using Pivot table
i've the following data:
|
id |
Customer |
item |
SalesAmount |
| 1 | Global inc | apple | 50 |
| 2 | Americana | banana | 10 |
| 3 | Americana | apple | 100 |
| 4 | Americana | apple | 60 |
| 5 | Global inc | apple | 10 |
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.
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.
Mynda
Thanks for your answer,
unfortunately when i try to apply your solution i get error:
Calculation error in measure 'Table1'[measure1]: A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
i uses Office professional plus 2019, excel version 1908
Not sure what you did wrong, but it works for me. See attached.
Mynda
