how to make measure that uses blank() to appear for Total

[Anonymous]

I have the following measure:

Dollars =
VAR GivenMonthRelative = MIN('Dimension Date'[Month Relative Label])

RETURN
IF(
    GivenMonthRelative = "Current Month"
    ,BLANK()
    ,SUM('Fact'[Cost])
)

The purpose of the code beyond the simple SUM('Fact'[Cost]) expression is to make the measure not appear for the current month on a visual that uses 'Dimension Date'[Month Relative Label].  I have this measure (and a few other measures) on a table visual that also has [Month Relative Label] as a dimension.  When I turn on Totals for the table, this measure's Total value is not appearing.  How can I make its Total value appear?

For reference, when I re-define the measure as simply Dollars = SUM('Fact'[Cost]), the measure Total value appears.  So, it must have something to do with the BLANK() expression, but I don't know how to resolve it.

[Anonymous]

// Do this:

Dollars =
CALCULATE(
	sum( Fact[Cost] ),
	KEEPFILTERS(
		'Dimension Date'[Month Relative Label] <> "Current Month"
	)
)

... and be done with it. The other solutions are not only overly complex. They are also slower than this one. The one above is about as good as it can get.

Best

D

[jdbuchanan71]

@Anonymous 

Try changing your VAR to use SELECTEDVALUE instead of MIN.

VAR GivenMonthRelative = SELECTEDVALUE('Dimension Date'[Month Relative Label])

RETURN
IF(
    GivenMonthRelative = "Current Month"
    ,BLANK()
    ,SUM('Fact'[Cost])
)

[Anonymous]

@jdbuchanan71 
Even though this solution enables the Total value to appear, it includes the current month's value.  I don't want the Total to include the current month's value.  Sorry that I didn't make this clear in my original post.

[jdbuchanan71]

Although in my test your original measure works correctly for the total row:

[azradadya48]

Hi guys,
I am struggling the same issue.  Although i change the data as Blank, total value include the original value.
If you solved/understand the issue, your help is much appreciable.

Thanks

[az38]

Hi @Anonymous 

try to use SUMX. smth like

Dollars =
VAR GivenMonthRelative = MIN('Dimension Date'[Month Relative Label])

RETURN
SUMX('Fact',
IF(
    GivenMonthRelative = "Current Month"
    ,BLANK()
    ,SUM('Fact'[Cost])
)
)

[Anonymous]

@az38 

This solution didn't work.