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.
Hello,
I need help creating the following measures in Power Query and Power BI separately:
Measure
Investment $ | Budget Dollars | Cost Variance | % Change | Measure |
$34,500,352 | $20,176,565 | $14,323,787 | 42% | |
$65,000 | $3,949,110 | ($3,884,110) | -5976% | |
$34,565,352 | $24,125,675 | $10,439,677 | 30% |
See attached picture with data sample
Column = SWITCH ( TRUE (), 'Table'[Cost Variance] <= -10000 && 'Table'[% Change] <= -.1, "Under Budget", 'Table'[Cost Variance] >= 10001 && 'Table'[% Change] >= .1, "Over Budget", 'Table'[Cost Variance] >= 9999 && 'Table'[Cost Variance] <=1000, "On Target", BLANK () ) Measure = SWITCH ( TRUE (), MIN ( 'Table'[Cost Variance] ) <= -10000 && MIN ( 'Table'[% Change] ) <= -.1, "Under Budget", MIN ( 'Table'[Cost Variance] ) >= 10001 && MIN ( 'Table'[% Change] ) >= .1, "Over Budget", MIN ( 'Table'[Cost Variance] ) >= 9999 && MIN ( 'Table'[Cost Variance] ) <=1000, "On Target", BLANK () )
Hope this helps.
Good Luck!
Thanks for the quick response. I just want to make sure am doing this correctly because it's showing an error message
The above was DAX
In the Query Editor the formula would be...
= if [Cost Variance] <= -10000 and [#"% Change"] <= -.1 then "Under Budget"
else if [Cost Variance] >= 10001 and [#"% Change"] >= .1 then "Over Budget"
else if [Cost Variance] >= 9999 and [Cost Variance] <= 10000 then "On Target"
else null
It worked everywhere except where the % Change field = "null". How can I correct this error?
You have to filter out the null values.
I'm not sure it's related to your error, but shouldn't the On Target statement cover the range from minus9999 to 10,000?
Hi @mdrammeh
Just to make sure I understand well your requirement. Do you want to create a measure or a calculated column ?
See here the difference and which option better fits your requirement: http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/
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 |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |