Reference a measure or write directly into your dax code?

[Anonymous]

If you had asked me this morning whether there would be any difference between writing a DAX statement in a separate measure and referencing it to directly into your DAX code, I would have told you there wasnt.  I would have been wrong. 

To showcase this, I was writing a measure today with the purpose of returning the Average of the total Revenue of each month.  Here is the first formula i tried:

Average Revenue = IF(
	COUNTROWS(VALUES('Monthly Sales'[YearMonth])) = 1, 
	SUM('Monthly Sales'[TOTALS]), 
	AVERAGEX(
		VALUES('Monthly Sales'[YearMonth]), 
		SUM('Monthly Sales'[TOTALS])
	)
)

This does not provide the correct answer.  So, i took the SUM portion of the function and made it a measure.  This gave me:

Revenue = SUM('Monthly Sales'[TOTALS])

Average Revenue = IF(
	COUNTROWS(VALUES('Monthly Sales'[YearMonth])) = 1, 
	[Revenue], 
	AVERAGEX(
		VALUES('Monthly Sales'[YearMonth]), 
		[Revenue]
	)
)

This change now gives me the correct answer.

I thought it was a good topic to highlight and share.

[v-huizhn-msft]

Hi @Anonymous,

It's really a good idea, thanks for sharing it.

Best Regards,
Angelia