The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I'm trying to make a running total measure disregard the month slicer selection.
I have this base dataset:
Here is my running total without month selection (works fine):
The problem comes when I click on the slicer, like this:
I want it to look like this (photoshopped):
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" )
Solved! Go to Solution.
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
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!
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |