how to make measure return 0 but not for applicable months

[Anonymous]

I have some measure, say [Total Sales].  This measures appears on a table visual, along with field 'Dimension Month'[Month Name].  This dimension table will have more months than those in 'Fact Sales' table (e.g., future months).  For those in 'Fact Sales', 'Dimension Month'[Has Sales] = "Yes"; for those that are not in, 'Dimension Month'[Has Sales] = "No".  Even for those that are in 'Fact Sales', there might not be any sales (e.g., [Total Sales] will be blank).

I want [Total Sales] to return zero for months with no sales, but only where 'Dimension Month'[Has Sales] = "Yes"; otherwise, I want [Total Sales] to be blank.

For example, instead of 

Month Name          Has Sales       Total Sales

A                              Yes                25

B                               Yes                blank

C                               No                 blank

I want:

Month Name          Has Sales       Total Sales

A                              Yes                25

B                               Yes                0

C                               No                 blank

How can I achieve this?

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Calculate your [Total Sales] and store that in a VAR, test to see if it is BLANK and check 'Dimension Month'[Has Sales] for Yes/No.  Then return the appropriate value. Something like this

Total Sales =

VAR TS = SUM('Table'[Sales])

RETURN

	SWITCH(

	TRUE(),

	ISBLANK(TS) && 'Dimension Month'[Has Sales] = "Yes", 0,

	TS

	)

regards

Phil