Skip to Content
0

Formula to calcuate Actual vs Plan Percentage

Nov 27, 2016 at 07:10 AM

134

avatar image
Former Member

Hi Team,

I have one of the requirement, where

Actual sales = 100 Dollars

Plan sales = 50 Dollars.

then sales percentage share is 100/50 * 100 = 200%

i.e. percentage is becoming more than 100. so in this case I want to show only 100%

please provide some solution in BEX Query designer how to consider more than 100% as 100 value.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Satya K Nov 29, 2016 at 12:09 PM
0

Hi Pamarthi,

I hope you have already created % Share keyfigure. Hide this keyfigure and create a new formula by making use of %share keyfigure.

New Formula % Share = ( ( NODIM(% Share Actual Keyfigure) > 100 ) * 100 ) + % Share Actual Keyfigure )

With the above formula, if % share is more than 100% then 100 will be displayed else the actual % share will be displayed. Please try the above formula and let me know.

Regards,

Satya.

Share
10 |10000 characters needed characters left characters exceeded
seshu P Nov 28, 2016 at 08:42 AM
0

Hi ,

By using boolean operators we can acheive this refer this

https://help.sap.com/saphelp_nw70/helpdata/en/23/17f13a2f160f28e10000000a114084/content.htm

Share
10 |10000 characters needed characters left characters exceeded
Diego Seben
Nov 28, 2016 at 01:57 PM
0

Hi,

After you use the boolean, make sure to choice if you want a formula (use locally) or a calculated key figure (use globally) as below:

https://wiki.scn.sap.com/wiki/display/BI/Formulas,+Calculated+Key+Figures+and+Restricted+key+Figures

Share
10 |10000 characters needed characters left characters exceeded
Loed Despuig Nov 29, 2016 at 06:42 AM
0

Hi,

I assume that you are using the %A (percentage share formula, right?) formula.

If yes, use this equation:

FORMULA = ((ACTUAL_SALES / PLAN_SALES) > 1) * (1 %A 1) + ((ACTUAL_SALES / PLAN_SALES) %A 1)

If it didn't work, you need to make four (4) formulas:

FORMULA1 = ACTUAL_SALES / PLAN_SALES

FORMULA2 = 1 %A 1

FORMULA3 = FORMULA1 %A 1

FINAL_FORMULA = FORMULA1 > 1 * FORMULA2 + FORMULA3

Just post here for questions.

Regards,

Loed

Share
10 |10000 characters needed characters left characters exceeded