Latest Enterprise DNA Initiatives

Percentage Change - Flipped

DAX Calculations
#1

Hi DNA Team,

I need some help on this silently different percentage change calculation.

I have completed a simple Percentage Change Measure in the Attached PBIX file. Percent Change.pbix (30.3 KB)

By way of example, I would like a measure that takes the percentage change calculated and presents it in an opposite manner , for instance:

  • if the % Change equalled 0% then it would equals 100%.

  • or if % Change equalled 1% then it would equal 99%.

  • or if % Change equalled 2% then it would equal 98%…and so on…

If you can demonstrate this in the PBIX file that would be great.!

Kind regards

Adam

#2

Hi @adam,

Thanks for providing a PBIX :+1:
You didn’t specify how you wanted to deal with negative values, so I applied the same logic regardless of the sign. I’m sure you can adjust that to your needs, if you want to deal with that differently.

Percent Change Flipped =
VAR Perc = SUM ( 'Cost G3'[Total Contract Sum at G3] ) / SUM ( 'Cost G2'[Total Contract Sum at G2] ) -1
VAR Sign = IF( Perc <0 , -1, 1 )
VAR New = 1-ABS( Perc )
VAR Result = IF( Sign <0 , -New, New )
RETURN

Result

Here’s your sample file. Percent Change.pbix (30.6 KB)
I hope this is helpful.

#3

Hi Melissa,

Quite right on the negative numbers! That measure looks great.

Kind regards

Adam