Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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, I have visual level filters applied to this table.

 

The running total seems to be working fine until it reaches 36, the visual filters means there are no rows for this but i would expect the running total to continue by adding 0 as opposed to applying 679, the value to anything less than 0 Closed deal Time.

 

 

 

 

Here is the expression i'm using to calculate the Running Total 

 

Running Total =
CALCULATE([SnapshotDealRowCount],
            FILTER(ALL('SnapshotDeal'[ClosedDealOpenTime]),
             'SnapshotDeal'[ClosedDealOpenTime] <= MAX('SnapshotDeal'[ClosedDealOpenTime])
            )
)
 
[SnapshotDealRowCount] is a simple COUNTROWS measure - COUNTROWS(SnapshotDeal)
 
Is it possible to continue the running total when there are blanks?  Any help would be greatly appreciated.
Need Help
Tips and Tricks
  • johnt75's avatar
    johnt75
    3 years ago

    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

5 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Is the column in the visual 'SnapshotDeal'[ClosedDealOpenTime] or is it a different column, maybe from a dimension table ? If it is a different column then use that column in the MAX function rather than 'SnapshotDeal'[ClosedDealOpenTime].

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        johnt75
        Super User
        3 years ago

        I don't quite understand how the value 36 is appearing if no rows correspond to that number. Can you share a sample PBIX file ?