Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
llewilliams95
Regular Visitor

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.

 

llewilliams95_0-1685528238386.png

 

 

 

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.
1 ACCEPTED SOLUTION

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

View solution in original post

5 REPLIES 5
johnt75
Super User
Super User

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].

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.

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 ?

Here is a dropbox link to the sample file

 

https://www.dropbox.com/s/ikj8ly93pazz0q3/Sample.pbix?dl=0 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.