Forum Discussion

EvaHello's avatar
EvaHello
Helper I
9 months ago
Solved

If OR statement - what am i doing wrong?

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...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    9 months ago

    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's avatar
Shahid12523
Community Champion
9 months ago

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