If OR statement - what am i doing wrong?

[EvaHello]

Hi all,

I am trying to write a column formula to say if the metric is labelled 'percentage', then format the absolute difference as "-", and if its any % eg gross margin, the variance calculation as a %.  If its labelled 'Ratio', i just want the variance to be "-" regardless.  I know the orange part works but the remaining part i tried to cleverly add on is giving me an error.  Can anyone correct this please?

% Variance FC vs PY =

VAR MetricType = SELECTEDVALUE('Results (2)'[Period])

VAR FC = SELECTEDVALUE('Results (2)'[MTD FC])

VAR PY = SELECTEDVALUE('Results (2)'[MTD PY])

RETURN

IF(

MetricType = "Percentage",

FORMAT(FC - PY, "-"),

FORMAT(DIVIDE(FC - PY, PY), "0%;(0%)")

IF(

MetricType = "Metric",

FORMAT(FC - PY, "-"),FORMAT(FC - PY, "-"),

)

[danextian]

Hi @EvaHello 

What do you expect "-" to return? Using this simply results a  literal string. You also mentioned absolute difference but there isn't anything in your formula that indicates it. It would be easier for us to figure out what you're trying to achieve had you provided a sample data and your epxected result. Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[EvaHello]

Hi - id like to adjust the formula so that if its a cost, the variance takes account of negatives

Current formula: 

IF(or(

MetricType = "Percentage",MetricType = "Ratio"),

FORMAT(FC - PY, "-"),

FORMAT(DIVIDE(FC - PY, PY), "0%;(0%)")

)

Data :

	Budget	Forecast	Variance	What I want it to show
Rev	405.3	383.7	6%	6%
Cost	-8	-15	-47%	47%
Profit	397.3	368.7	8%	8%

[ryan_mayu · Accepted solution]

@EvaHello 

pls see if this works for you

% Variance FC vs PY =
VAR MetricType = SELECTEDVALUE('Results (2)'[Period])
VAR FC = SELECTEDVALUE('Results (2)'[MTD FC])
VAR PY = SELECTEDVALUE('Results (2)'[MTD PY])
RETURN
IF(
OR(MetricType = "Percentage", MetricType = "Ratio"),
"-",
FORMAT(DIVIDE(FC - PY, PY), "0%;(0%)")
)

if this does not work, pls provide some sample data and expected output.

[Shahid12523]

% Variance FC vs PY =
VAR MetricType = SELECTEDVALUE('Results (2)'[Period])
VAR FC = SELECTEDVALUE('Results (2)'[MTD FC])
VAR PY = SELECTEDVALUE('Results (2)'[MTD PY])
RETURN
SWITCH(
TRUE(),
MetricType = "Percentage", FORMAT(FC - PY, "-"),
MetricType = "Ratio", FORMAT(FC - PY, "-"),
MetricType = "Metric", FORMAT(FC - PY, "-"),
FORMAT(DIVIDE(FC - PY, PY), "0%;(0%)")
)

[lbendlin]

Percentage change combined with sign change between the values will always result in meaningless numbers, same as when one of the values is zero.    For example, what would be the percentage change from -50 to +20 ?  How about from -30 to +20 ?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[EvaHello]

Just to add on to this - id like to factor into my formula that if its a cost and it goes up, the variance should be negative.  At present, its a blanket variance where its positive wherever anything goes up (revenue, costs etc).  Could anyone help alter this for me?