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
AltGr9
Helper I
Helper I

Getting running total to disregard filter

I'm trying to make a running total measure disregard the month slicer selection.

 

I have this base dataset:

 

Running total 1.PNG

 

Here is my running total without month selection (works fine):

 

Running total 2.PNG

The problem comes when I click on the slicer, like this:

 

Running total 3.PNG

I want it to look like this (photoshopped):

 

Running total 4.PNG

 

 

 

 

 

 

 

 

In other words, I want the slicer to affect the Actual column, but not the Running Total of the actual. 

 

Here is the Dax code I have of the running total:

 

RT of Actual = 
CALCULATE(
	SUM('Dataset'[Amount])
	,FILTER(
		ALLSELECTED('Dataset'[MonthID]),
		ISONORAFTER('Dataset'[MonthID], MAX('Dataset'[MonthID]), DESC)
	)
    ,'Dataset'[Capex Type] = "Act"
)

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

First, please turn MonthID into an integer (of the same shape as your text) or add a column that will store the same value as integer (but I'd go with the first option) and HIDE IT.

 

Here's the measure but please do the above:

 

-- measure 1

[Amount] := SUM('Dataset'[Amount])

-- measure 2

RT of Actual =
var __lastVisibleMonthID = MAX( 'Dataset'[MonthID] )
return
CALCULATE(
	[Amount],	
	ALL( 'Dataset'[Month] ),
	'Dataset'[Month] <= __lastVisibleMonthID,
        'Dataset'[Capex Type] = "Act"
)

Best

Darek

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

First, please turn MonthID into an integer (of the same shape as your text) or add a column that will store the same value as integer (but I'd go with the first option) and HIDE IT.

 

Here's the measure but please do the above:

 

-- measure 1

[Amount] := SUM('Dataset'[Amount])

-- measure 2

RT of Actual =
var __lastVisibleMonthID = MAX( 'Dataset'[MonthID] )
return
CALCULATE(
	[Amount],	
	ALL( 'Dataset'[Month] ),
	'Dataset'[Month] <= __lastVisibleMonthID,
        'Dataset'[Capex Type] = "Act"
)

Best

Darek

Thank you so much. This worked like a dream!

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.

Top Solution Authors