Calculating Values if cell in a different Row equals "X"

[refoer]

I'm trying to calculate a value in one column based on what value is present on the same row in a different colum.

Ie: Based on what is in column "Branch" cell 1, divide column Stock Available cell 1 by N 

This is what I was working with:

Column = if(Query1[Branch]="75 Saskatoon", Query1[StockAvailable]/294, if(Query1[Size]="25 Winnipeg, Query1[StockAvailable]/189, Query1[StockAvailable])))

[Anonymous · Accepted solution]

Hi @refoer 

There are two mistakes in your calculated column.

First is like  lbendlin 's reply, you should put your text value in quotes. Second is that in the end of your dax, you use "". You calculated column will return to number type, "" will be text type. You can try to use 0 or blank to replace "".

I update your calculated column.

Column = 
SWITCH (
    Query1[Size] ,
    "2X4", Query1[StockAvailable] / 294,
    "2X6", Query1[StockAvailable] / 189,
    "2X8", Query1[StockAvailable] / 147,
    "2X10", Query1[StockAvailable] / 105,
    "2X12", Query1[StockAvailable] / 84,BLANK()
)
/*IF(Query1[Size] = "2X4", Query1[StockAvailable] / 294,
    IF(Query1[Size] = "2X6", Query1[StockAvailable] / 189,
    IF(Query1[Size] = "2X8", Query1[StockAvailable] / 147,
    IF(Query1[Size] = "2X10", Query1[StockAvailable] / 105,
    IF(Query1[Size] = "2X12", Query1[StockAvailable] / 84,BLANK())))))*/

Here are two ways to achieve your goal, use switch function may be better.

My sample and result is as below.

Best Regards,

Rico Zhou

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

[refoer]

Rico, this was extremely helpful, thank you

[Ashish_Mathur]

Hi,

Try this calculated column formula

Column = if(Query1[Branch]="75 Saskatoon", Query1[StockAvailable]/294, if(Query1[Size]="25 Winnipeg", Query1[StockAvailable]/189, Query1[StockAvailable]))

[lbendlin]

put your text values in quotes

instead of  ... = 2X6   write ... = "2X6"