Issue with the ROUND function

DAX
1

@mspanic

MicrosoftTeams-image (13)
MicrosoftTeams-image (13)826×252 9.79 KB

I am geting the error above for these codes. Where am i going wrong?

BlockquoteDynamic Forecast measure =
SWITCH(
SELECTEDVALUE(Dynamic_Selection_Period[Period]),
“Prediction”,
“Forecast: " &
IF(
ISERROR([Time Metric Calculations FCST]),
“Error”,
ROUND([Time Metric Calculations FCST], 2)
) &
" (” &
IF(
ISERROR([Time Metric Calculations CY Forecast] - [Time Metric Calculations FCST]),
“Error”,
ROUND(([Time Metric Calculations CY Forecast] - [Time Metric Calculations FCST]), 2)
) &
", " &
IF(
ISERROR(([Time Metric Calculations CY Forecast] - [Time Metric Calculations FCST])/[Time Metric Calculations FCST]),
“Error”,
ROUND(([Time Metric Calculations CY Forecast] - [Time Metric Calculations FCST])/[Time Metric Calculations FCST], 2)
) &
“%)”,
“MTD”,
“Forecast: " &
IF(
ISERROR([Time Metric Calculations FCST]),
“Error”,
ROUND([Time Metric Calculations FCST], 2)
) &
" (” &
IF(
ISERROR([Time Metric Calculations CY] - [Time Metric Calculations FCST]),
“Error”,
ROUND([Time Metric Calculations CY] - [Time Metric Calculations FCST], 2)
) &
", " &
IF(
ISERROR(([Time Metric Calculations CY] - [Time Metric Calculations FCST])/[Time Metric Calculations FCST]),
“Error”,
ROUND(([Time Metric Calculations CY] - [Time Metric Calculations FCST])/[Time Metric Calculations FCST], 2)
) &
“%)”
)

Blockquote ```
Dynamic Forecast measure = SWITCH(SELECTEDVALUE(Dynamic_Selection_Period[Period]),“Prediction”,“Forecast: " & ROUND([Time Metric Calculations FCST],2) & " (” & ROUND(([Time Metric Calculations CY Forecast] - [Time Metric Calculations FCST]),2) & ", " & ROUND(([Time Metric Calculations CY Forecast] - [Time Metric Calculations FCST])/[Time Metric Calculations FCST],2) & “%)”,“MTD”,“Forecast: " & ROUND([Time Metric Calculations FCST],2) & " (” & ROUND([Time Metric Calculations CY] - [Time Metric Calculations FCST],2) & ", " & ROUND(([Time Metric Calculations CY] - [Time Metric Calculations FCST])/[Time Metric Calculations FCST],2) & “%)”)


@mspanic
2

Hi @Yrstruly,

Welcome back to Forum.

Mdx error descriptions are usually very general - specially when it is confused with data types or with divide by 0 situation.

Check your SWITCH statement - what are you try to achieve - you compare SELECTEDVALUE(Dynamic_Selection_Period[Period]) with what ?

3

Blockquote Time Metric Calculations FCST =

–MONTH TO DATE

VAR VAR_MTD = SWITCH(SELECTEDVALUE(‘Calc Summary Metrics’[Metric])

–STOCK RELATED METRICS AND LINKED TO THE GLOBAL HOLDINGS TRANSACTIONAL TABLE ONLY–remain as a daily view

,“Average Field Stock”,CALCULATE([Metric Calculation (FCST)],Filter_Calendar_Date[Financial Year]=MAX(Filter_Calendar_Date[Financial Year]),Filter_Calendar_Date[Period]=MAX(Filter_Calendar_Date[Period]))

–FLOW RELATED METRICS AND LINKED TO THE FIELD FLOWS TRANSATIONAL TABLE ONLY - this needs to adjust to a avg view

,“Issues”,CALCULATE([Metric Calculation (FCST)],Filter_Calendar_Date[Financial Year]=MAX(Filter_Calendar_Date[Financial Year]),Filter_Calendar_Date[Period]=MAX(Filter_Calendar_Date[Period]))

,“Returns”,CALCULATE([Metric Calculation (FCST)],Filter_Calendar_Date[Financial Year]=MAX(Filter_Calendar_Date[Financial Year]),Filter_Calendar_Date[Period]=MAX(Filter_Calendar_Date[Period]))

)

–FULL PERIOD

VAR VAR_Pred = SWITCH(SELECTEDVALUE(‘Calc Summary Metrics’[Metric])

–STOCK RELATED METRICS AND LINKED TO THE GLOBAL HOLDINGS TRANSACTIONAL TABLE ONLY–remain as a daily view

,“Average Field Stock”,CALCULATE([Metric Calculation (FCST)],Filter_Calendar_Date[Financial Year]=MAX(Filter_Calendar_Date[Financial Year]),Filter_Calendar_Date[Period]=MAX(Filter_Calendar_Date[Period]))

–FLOW RELATED METRICS AND LINKED TO THE FIELD FLOWS TRANSATIONAL TABLE ONLY - this needs to adjust to a avg view

,“Issues”,CALCULATE([Metric Calculation (FCST)],Filter_Calendar_Date[Financial Year]=MAX(Filter_Calendar_Date[Financial Year]),Filter_Calendar_Date[Period]=MAX(Filter_Calendar_Date[Period]),REMOVEFILTERS(Filter_Calendar_Date[Week Number]))

,“Returns”,CALCULATE([Metric Calculation (FCST)],Filter_Calendar_Date[Financial Year]=MAX(Filter_Calendar_Date[Financial Year]),Filter_Calendar_Date[Period]=MAX(Filter_Calendar_Date[Period]),REMOVEFILTERS(Filter_Calendar_Date[Week Number]))

)

RETURN

SWITCH(SELECTEDVALUE(Dynamic_Selection_Period[Period]),“MTD”,VAR_MTD,“Prediction”,VAR_Pred)/1000000

Time Metric Calculations CY =

VAR VAR_MTD =

SWITCH(SELECTEDVALUE(‘Calc Summary Metrics’[Metric])

–STOCK RELATED METRICS AND LINKED TO THE GLOBAL HOLDINGS TRANSACTIONAL TABLE ONLY–remain as a daily view

,“Average Field Stock”,[Metric Calculation]–CALCULATE([Metric Calculation],Filter_Calendar_Date[Financial Year]=MAX(Filter_Calendar_Date[Financial Year]),Filter_Calendar_Date[Period]=MAX(Filter_Calendar_Date[Period]))–/(MAX(Transactional_Global_Holding[Week Number])*7)

–FLOW RELATED METRICS AND LINKED TO THE FIELD FLOWS TRANSATIONAL TABLE ONLY - this needs to adjust to a avg view

,“Issues”,[Metric Forecast Actuals Calculation]

,“Returns”,[Metric Forecast Actuals Calculation]

)

RETURN

SWITCH(SELECTEDVALUE(Dynamic_Selection_Period[Period]),“MTD”,VAR_MTD,“Prediction”,VAR_MTD)/1000000

Time Metric Calculations CY Forecast =

VAR VAR_MTD_i_r = [Metric Forecast Future Calculation] + [Metric Forecast Actuals Calculation]

VAR VAR_MTD_AVG_Q =(([Metric Forecast Future Calculation] + [Metric Forecast Actuals Calculation]))/MAX(Filter_Calendar_Date[Number of days])

VAR VAR_ADJUST_INDIA = 0

VAR VAR_ADJUST_MENA = 0

VAR VAR_ADJUST_SSA = 0

VAR VAR_ADJUST_TURKIYE = 0

RETURN

SWITCH(SELECTEDVALUE(Filter_Company_Code[Region])

,“Middle East and North Africa”,SWITCH(SELECTEDVALUE(‘Calc Summary Metrics’[Metric]),“Average Field Stock”,VAR_MTD_AVG_Q,“Issues”, VAR_MTD_i_r,“Returns”,VAR_MTD_i_r)/1000000

,“India”,SWITCH(SELECTEDVALUE(‘Calc Summary Metrics’[Metric]),“Average Field Stock”,VAR_MTD_AVG_Q,“Issues”,VAR_MTD_i_r,“Returns”,VAR_MTD_i_r)/1000000

,“Sub-Saharan Africa”,SWITCH(SELECTEDVALUE(‘Calc Summary Metrics’[Metric]),“Average Field Stock”,VAR_MTD_AVG_Q,“Issues”,VAR_MTD_i_r,“Returns”,VAR_MTD_i_r)/1000000

,“Türkiye”,SWITCH(SELECTEDVALUE(‘Calc Summary Metrics’[Metric]),“Average Field Stock”,VAR_MTD_AVG_Q,“Issues”,VAR_MTD_i_r,“Returns”,VAR_MTD_i_r)/1000000)

4

Hi @Yrstruly ,

Little bit confused with all this new metrics.

Regarding first post:

What are the values for Dynamic_Selection_Period[Period] ?

Check if the following columns have numeric data types :

  • [Time Metric Calculations FCST]

  • [Time Metric Calculations CY Forecast]

Also it is strongly advised to use DIVIDE function instead of just /
So instead for example

(Metric Calculations CY] - [Time Metric Calculations FCST])/[Time Metric Calculations FCST]

use DIVIDE (Metric Calculations CY] - [Time Metric Calculations FCST],[Time Metric Calculations FCST], BLANK())

to avoid divide by 0 error

5

Hi @Yrstruly

Please remember not to directly address your posting to a certain member of the forum. It discourage others in helping you.

Thanks
Keith