Forum Discussion
dax expressions
Is there a cleaner way to write this expressions?
YTD_Variance_Matrix = Calculate(TOTALYTD([MTD_Actuals_Matrix],V_FS_DIM_DATE[act_date])-TOTALYTD([MTD_Budget_Matrix],V_FS_DIM_DATE[act_date]),KPI_Finance_Matrix[Group]="Revenue")&
(Calculate(TOTALYTD([MTD_Budget_Matrix],V_FS_DIM_DATE[act_date])-TOTALYTD([MTD_Actuals_Matrix],V_FS_DIM_DATE[act_date]),KPI_Finance_Matrix[Group]="Operating Expenses"))
THe output is coming out as a text value, need to convert to a numeric, for formatting purposes
- Anonymous7 years ago
First of all, I put your code in www.daxformatter.com to come up with the following:
YTD_Variance_Matrix = CALCULATE ( TOTALYTD ( [MTD_Actuals_Matrix], V_FS_DIM_DATE[act_date] ) - TOTALYTD ( [MTD_Budget_Matrix], V_FS_DIM_DATE[act_date] ), KPI_Finance_Matrix[Group] = "Revenue" ) & ( CALCULATE ( TOTALYTD ( [MTD_Budget_Matrix], V_FS_DIM_DATE[act_date] ) - TOTALYTD ( [MTD_Actuals_Matrix], V_FS_DIM_DATE[act_date] ), KPI_Finance_Matrix[Group] = "Operating Expenses" ) )You can see about halfway down you use the character "&".
This will concatenate the 2 otherwise numeric values into a single text string, probably not what you want.
Consider replacing that & with a + or a - depending on the arithmetic you need to perform.
2 Replies
- AnonymousNot applicable
First of all, I put your code in www.daxformatter.com to come up with the following:
YTD_Variance_Matrix = CALCULATE ( TOTALYTD ( [MTD_Actuals_Matrix], V_FS_DIM_DATE[act_date] ) - TOTALYTD ( [MTD_Budget_Matrix], V_FS_DIM_DATE[act_date] ), KPI_Finance_Matrix[Group] = "Revenue" ) & ( CALCULATE ( TOTALYTD ( [MTD_Budget_Matrix], V_FS_DIM_DATE[act_date] ) - TOTALYTD ( [MTD_Actuals_Matrix], V_FS_DIM_DATE[act_date] ), KPI_Finance_Matrix[Group] = "Operating Expenses" ) )You can see about halfway down you use the character "&".
This will concatenate the 2 otherwise numeric values into a single text string, probably not what you want.
Consider replacing that & with a + or a - depending on the arithmetic you need to perform.
- AnonymousNot applicable
Thank You CHris