Calculated Column with multiple conditions

[eWise]

Hi all,

Am trying to calculate a cost column based on multiple conditions but it seems like the last step where I need to calculate the results from coulmn values throws the whole thing off.

When I remove step 

'Order'[Document.orderDetails.totalMileage]* 2.5, to test, it works. Some of the columns required in the conditions are text because I have to look at customer name and also city to determine what values to apply. I will appreciate any help. Thanks

[v-yangliu-msft]

Hi  @eWise ,

Has your problem been solved? If it is solved, you can mark the correct answer.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ryan_mayu]

@eWise 

I guess it returns two type of data, if  "*2.5", will be numerical type., otherwise will be "125" which is Text.

We can't set two different data types in one column

[eWise]

I ended up formating it as below which worked but some of the columns are wrongly calculated;

FORMAT(INT('Order'[Document.orderDetails.totalMileage] * 2.5 + 65), "0")

Mileage 1374 and 191 should be 125 since no condition is met and mileage 2.5 should be 65. Am not sure what is causing that.

[ryan_mayu]

@eWise 

Since you are doing the calculation. I am not sure why you want to transfer numerical type to text?

could you pls provide the pbix file?

[eWise · Accepted solution]

@ryan_mayu 
I figured out where I was going wrong. I was comparing a numeric column(

'Order'[Document.orderDetails.totalMileage]) to text "12". Once I changed "12" to 12, everything worked. You mentions of numeric and text prompted me to review the query and then I was able to see my mistakes. Thank you.

[ryan_mayu]

you are welcome