7 Replies Latest reply: Mar 7, 2017 12:47 PM by Dave Melillo

# Set Analysis to Show % Change Automatically

I have a pivot table (pictured below) with 1 dimension, OppCreateMonth and one measure:

=sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])

Is there a way to have an expression that automatically calculates the percentage change between each cell as you move right? This would allow me to write ONE expression and then graph the percentage change between periods at any level.

Thank you,

- dave

• ###### Re: Set Analysis to Show % Change Automatically

May be this

=Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])/

Before(Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])) - 1

or

=Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])/

After(Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])) - 1

• ###### Re: Set Analysis to Show % Change Automatically

Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])/

Before(Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])) - 1

The BEFORE and AFTER commands are what I was missing.

Thanks as always Sunny

• ###### Re: Set Analysis to Show % Change Automatically

Before and After are function unique to Pivot table, so don't get noticed by a lot of people. Similarly, there is ColumnNo() function which works like RowNo() but with a pivoted dimension....

• ###### Re: Set Analysis to Show % Change Automatically

Is there a similar function to Before and After in a straight table?

• ###### Re: Set Analysis to Show % Change Automatically

Above and Below

• ###### Re: Set Analysis to Show % Change Automatically

Ha thank you!

• ###### Re: Set Analysis to Show % Change Automatically

Hi,

Check this

```IF((Above(Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)]))-Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)]))<0,-(Above(Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)]))-Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])),
(Above(Sum(Sales)/Sum(TOTAL Sales))-Sum(Sales)/Sum(TOTAL Sales)))
```

Using this in combo chart line expression, you can have line for only difference on bar chart.

Attached the sample QVW.

HTH,

PFA,

Hirish