legalhustler
Well-known Member
- Joined
- Jun 5, 2014
- Messages
- 1,214
- Office Version
- 365
- Platform
- Windows
I have a data model pivot table with a column named "2019 Actual" which shows sales amount. The column resides on the "CombinedAllYears" table. I don't technically have dates (4/1/2019, 4/2/2019, etc) in the table, just month names "Jan", "Feb" etc. I also have a "Store" slicer and want the measure to respect the slicer selection. I am having trouble creating a measure to get the running total for the "2019 Actual" measure. The measure I have listed below works pretty close to what I want. The problem is sometimes when the "2019 Actual" amount for a prior month is greater than the subsequent month then the Running Total has it displayed incorrectly (see example below), otherwise if it is not then the measure does work properly. Can someone advise on how to fix this?
This is what the pivot table shows (note the first row is the headers)
Month....2019 Actual......Running Total
Jan........$70,000...........$140,000
Feb........$60,000...........$60,000
My current measure:
2019 Actual Running Total:=CALCULATE(sumx(CombinedAllYears,CombinedAllYears[2019 Actual]),filter(ALLSELECTED(CombinedAllYears),CombinedAllYears[2019 Actual]<=Max(CombinedAllYears[2019 Actual])))
This is what the pivot table shows (note the first row is the headers)
Month....2019 Actual......Running Total
Jan........$70,000...........$140,000
Feb........$60,000...........$60,000
My current measure:
2019 Actual Running Total:=CALCULATE(sumx(CombinedAllYears,CombinedAllYears[2019 Actual]),filter(ALLSELECTED(CombinedAllYears),CombinedAllYears[2019 Actual]<=Max(CombinedAllYears[2019 Actual])))
Last edited: