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 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, "-"),
)
  • 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.

6 Replies

  • 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?

  • 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

  • Shahid12523's avatar
    Shahid12523
    Community Champion

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

  • 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.

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

      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 :
       
       BudgetForecastVarianceWhat I want it to show
      Rev405.3383.76%6%
      Cost-8-15-47%47%
      Profit397.3368.78%8%