Calculate percentage of target achieved when target is a negative number

[emilvladu]

Hello everyone,

I have to report what is the achievement vs target but I have a problem when the targets or actual are negative.
There are eight scenarios as you can see below.

Is there any specific formula to sum up all this scenarios?

 

Thanks,

[dedelman_clng · Accepted solution]

If the formula not consistent, there will be no way to program it.  Can you build up a series of IF/THEN statements to describe how each formula is created?

[dedelman_clng]

What is your Excel calcuation for "Real Actual vs Target" ?

[emilvladu]

Please find bellow

[v-sihou-msft · Accepted solution]

@emilvladu

I'm not quite sure about your rule. I create a calculated column based on your sample data (a lot of nested IFs).

Actual Vs Target =
IF (
    'Table'[Actual] / 'Table'[Target]
        > 0,
    IF (
        'Table'[Actual] > 0
            && 'Table'[Target] > 0,
        'Table'[Actual] / 'Table'[Target],
        IF (
            'Table'[Actual] > 'Table'[Target],
            'Table'[Actual] / 'Table'[Target]
                + 1,
            - ( 'Table'[Actual] / 'Table'[Target] )
        )
    ),
    IF (
        'Table'[Actual] < 'Table'[Target],
        'Table'[Actual] / 'Table'[Target]
            - 1,
        1
            - 'Table'[Actual] / 'Table'[Target]
    )
)

Regards,

[Anonymous]

Hi, thanks for the nested ifs formula, 

I tried it myself and it works! but just a point on one of the formula (in red), 

I had to change it to (Actual-Target)/ABS(Target) + 1. 

it works for the example of Actual = -5 and Target = -10, 

However, it won't work when Actual = -8 and Target = -10. your formula will give 180% instead of 120% . 

Actual Vs Target =
IF (
    'Table'[Actual] / 'Table'[Target]
        > 0,
    IF (
        'Table'[Actual] > 0
            && 'Table'[Target] > 0,
        'Table'[Actual] / 'Table'[Target],
        IF (
            'Table'[Actual] > 'Table'[Target],
            'Table'[Actual] / 'Table'[Target]
                + 1,
            - ( 'Table'[Actual] / 'Table'[Target] )
        )
    ),
    IF (
        'Table'[Actual] < 'Table'[Target],
        'Table'[Actual] / 'Table'[Target]
            - 1,
        1
            - 'Table'[Actual] / 'Table'[Target]
    )
)

[emilvladu]

It works, Simon!

Thanks.

[dedelman_clng · Accepted solution]

If the formula not consistent, there will be no way to program it.  Can you build up a series of IF/THEN statements to describe how each formula is created?