MdxScript(Model) Calculation error in measure

[Anonymous]

Hi

I have a measure which was giving an error when I maintained straight forward calculation as "[Target] / [Sales %]", I thought it could be due to blank or 0 value in the Target and maintained a DAX code as below. This code is working fine for few product Id and not for few, ending up with the below error message.

Measure 1 =
IF(
OR(
ISBLANK([Target]),
[Target] == 0
),
BLANK(),
[Target] / [Sales %]
)

Target	Sales %	Measure 1
535	1.867%	28670
518	1.867%	27764
488	1.867%	26150

Any expert advise please.

Thanks
Mahad

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to the error message, it seems that the problem is caused by the data type mismatch... The required data type is numeric, but the actual data type of [Target] or [Sales %] is Text type.... You can follow the methods below:

1. Please make sure the previous two measures only have the numeric values and the correct data type be set for them just as suggestd by @amitchandak 

2. Update the formula of [Measure 1] as below if you can make sure they only have the numeric values. Later checck if it can work well...

Measure1 =
IF (
    OR ( ISBLANK ( [Target] ), [Target] = 0 ),
    BLANK (),
    DIVIDE ( value([Target]), value([Sales %]), 0 )
)

Best Regards

[amitchandak]

@Anonymous , Check the data type of these two  measures I doubt one of them is text

Also, use Divide

Divide([Target] , [Sales %])