Set Analysis to Show % Change Automatically

[Anonymous]

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

[sunny_talwar · Accepted solution]

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

[Anonymous]

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

[sunny_talwar]

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....

[Anonymous]

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

[sunny_talwar]

Above and Below

[HirisH_V7]

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

[Anonymous]

Ha thank you!