Search code examples
mathpowerbipowerquerym

convert percentage to range [-1;1]

I am working on a project in PowerBI. I want to convert a column of decimals (percentages) to a range from -1 to 1.

This is the table I'm using right now:

FirstName | Score
Jack      | 0.75
John      | 0.50
Reese     | 0.00
Mike      | 1.00

And the desired result is:

FirstName | Score
Jack      | 0.50
John      | 0.00
Reese     | -1.00
Mike      | 1.00

I am strugling with the math behind this. If I got that part, I could make it by using m-language in PowerBI.

Any ideas, suggestions?

Solution

  • Those scores don't actually look like percentages, they're more like fractions - for example, the percentage for 0.75 would be 75.

    So, if you're talking about mapping 0..1 to -1..1, it's a simple matter to apply the transformation:

    newVal = oldVal * 2 - 1

    The multiplication by two first scales it into the range 0..2 while the subtraction then shifts it into the range -1..1.