Forum Discussion

emillio's avatar
emillio
Frequent Visitor
9 years ago
Solved

format results above 100 with divide function

HI All, 

 

I'm using a Divide function to calculate the difference between my results and the budget. However, in some cases, my results appear above 100%. Even going up to 1000% above budget. 

 

Is there a way to format any numbers above or below 100 to show for instance as 100+ or 100- ? The formula I'm using is: 

 

∆ % = DIVIDE('KPI budget tabel'[Werkelijk]-'KPI budget tabel'[FC 1];'KPI budget tabel'[FC 1])

 

Thanks ! 

emillio. 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi emillio,

     

    You can try this:

    ∆ % = IF(DIVIDE('KPI budget tabel'[Werkelijk]-'KPI budget tabel'[FC 1],'KPI budget tabel'[FC 1])>1,100,-100)

     

    Regards,
    Yuliana Gu

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi emillio,

     

    You can try this formula.

    ∆ % = IF(DIVIDE('KPI budget tabel'[Werkelijk]-'KPI budget tabel'[FC 1],'KPI budget tabel'[FC 1])>1,"100+","100-")

     

    But you should be aware that the displayed values "100+", "100-" are text type, instead of numeric.

     

    Best regards,
    Yuliana Gu

    • emillio's avatar
      emillio
      Frequent Visitor
      9 years ago
      Thanks for that Yuliana,

      That will be a problem indeed, since the rest of my values will be percentages. Any chance I can just let it return 100 or -100 ? That will do the trick as well I guess.

      Regards,
      Emillio
      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        9 years ago

        Hi emillio,

         

        You can try this:

        ∆ % = IF(DIVIDE('KPI budget tabel'[Werkelijk]-'KPI budget tabel'[FC 1],'KPI budget tabel'[FC 1])>1,100,-100)

         

        Regards,
        Yuliana Gu