Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GY2019
Frequent Visitor

Column calculation based on text string

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.

 

NameQty
CA120
AK34
AZ17
FL38
NM75
AK12
AK77
FL28
CA5
AZ59
CA44

 

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

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

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
Frequent Visitor

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

Anonymous
Not applicable

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)
PowerBI.png

@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
Not applicable

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.