cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Reference a measure or write directly into your dax code?

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


1 REPLY 1
v-huizhn-msft Super Contributor
Super Contributor

Re: Reference a measure or write directly into your dax code?

Hi @Ross73312,

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

Best Regards,
Angelia