Log In
Register
Home
Active Member
March 19, 2023
Offline
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.
July 16, 2010
Offline
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
Active Member
March 19, 2023
Offline
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
1 Guest(s)
