Difference to first Row

[Wanli_Pu]

Hello,

I wonder whether there is a way to calculate differences to the first row, as illustrated in the example.

Thanks!

[AnwarAli]

Hi Wanli Pu,

Yes you can get such calculation by using calculated field with difference function in calculation-formula. I am sharing sample formula for reference but you can customize it ------ abs (difference(avg( Sales ), [Country ASC],-1) )

[Wanli_Pu]

@AnwarAli Thanks for your reply!

I am aware of this method, but what I want to do is to calculate the difference to the first row not previous row.

[AnwarAli]

Hi, I ranked records and find difference compared to record with rank 1. Hope it helps.

PS: you may merge formula for intermediate calculation steps and hide it from final output.

[Wanli_Pu]

Thanks! That’s pretty nice!

I also figured out a way to use difference and runningSum to do the same thing.

AVERAGE diff to prev runningSum of the diff
100 - -
200 100 100
125 -75 25
250 125 150
300 50 200

Since there are possible NULL values, I also have to use other calculated field to handle that.