Column calculation based on text string

[GY2019]

I am new on Power BI and I am trying to calculate cost based on the string name.  If the name equal:

"CA" then qty*9.84
"AK" then qty*12.14
"AZ" then qty*7.08
"FL" then qty*5.35
"NM" then qty*27

Else qty*1

sample table below.

Name	Qty
CA	120
AK	34
AZ	17
FL	38
NM	75
AK	12
AK	77
FL	28
CA	5
AZ	59
CA	44

I tried this but ran into errors.

cost = SUMX(Sheet1[Qty]*

(

SWITCH(VALUES(Sheet1[Name]),

"CA",9.84,

"AK",12.14,

"AZ",7.08,

"FL",5.35,

"NM",27,

1

))

Any advise and assistance would be very much appreciated.

thanks,

Gary

[AlB · Accepted solution]

Hi @GY2019 

Assuming it is a measure what you are looking for, try this:

cost =
SUMX (
    Sheet1,
    Sheet1[Qty]
        * SWITCH (
            Sheet1[Name],
            "CA", 9.84,
            "AK", 12.14,
            "AZ", 7.08,
            "FL", 5.35,
            "NM", 27,
            1
        )
)

[GY2019]

@AIB, that works. Thanks for the prompt response.

[Anonymous]

I add the custom column with such formula:

Result = MyTable[Qty] * switch(MyTable[Name]; "CA"; 2,5; "AK"; 7; "AZ"; 12,14; "FL"; 20,14; "NM"; 32,10)

[GY2019]

@Anonymous, I got an error 'syntac for ; is incorrect.  Also, I am trying to understand your formula for CA, why 2,5? or AZ, 12,14? 

[Anonymous]

@GY2019 

Applying ";" instead of "," is  only by regional settings 

https://community.powerbi.com/t5/Desktop/DAX-sintax-quot-quot-rather-than-quot-quot-syntax-error/td-...

I've jusr put some sample decimal values 🙂 to present the switch statement.

Of course I would recomend to keep this fixed factors in some seperate tables.

Regards