format results above 100 with divide function

[emillio]

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. 

[v-yulgu-msft]

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]

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 · Accepted solution]

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