Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |