Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
thanks,
Gary
Solved! Go to 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 ) )
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 ) )
@AIB, that works. Thanks for the prompt response.
@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?
Applying ";" instead of "," is only by regional settings
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |