Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

MdxScript(Model) Calculation error in measure

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 %]
)

 

TargetSales %Measure 1
5351.867%28670
5181.867%27764
4881.867%26150

 

 

 

Any expert advise please.

 

Thanks
Mahad

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

    Also, use Divide

    Divide([Target] , [Sales %])

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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