Forum Discussion

EvaHello's avatar
EvaHello
Helper I
10 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
    10 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.

lbendlin's avatar
lbendlin
Super User
10 months ago

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523