Forum Discussion
Running Total including blanks
Hi, I have an issue where i'm trying to calculate a Running Total but if i have a blank value for an attribute then the running total is ignored. Please see attached screenshot of my table visual...
The problem is that the MAX is returning a blank value when that time doesn't exist in the filtered data set. You can use
Running Total = VAR ReferenceTime = CALCULATE( MAX( 'SnapshotDeal'[ClosedDealOpenTime] ), ALLEXCEPT( 'SnapshotDeal', 'SnapshotDeal'[ClosedDealOpenTime] ) ) VAR Result = CALCULATE( [SnapshotDealRowCount2], 'SnapshotDeal'[ClosedDealOpenTime] <= ReferenceTime ) RETURN Result
The column in the visual is the same as 'SnapshotDeal'[ClosedDealOpenTime] used in the expression.
I have created a separate table containing all values from the ClosedDealOpenTime column and used the field from the new table in the MAX function but nothing changed. The Running Total still doesn't carry through the blank values.
johnt75
Super User
Super UserI don't quite understand how the value 36 is appearing if no rows correspond to that number. Can you share a sample PBIX file ?
Here is a dropbox link to the sample file
- johnt75
The problem is that the MAX is returning a blank value when that time doesn't exist in the filtered data set. You can use
Running Total = VAR ReferenceTime = CALCULATE( MAX( 'SnapshotDeal'[ClosedDealOpenTime] ), ALLEXCEPT( 'SnapshotDeal', 'SnapshotDeal'[ClosedDealOpenTime] ) ) VAR Result = CALCULATE( [SnapshotDealRowCount2], 'SnapshotDeal'[ClosedDealOpenTime] <= ReferenceTime ) RETURN Result