How to calculate % change in header total

Question & Answer
, , ,
1

How can i fix the % value in Total header. It actually just sums the % which is incorrect

image
image1824Ă—904 128 KB

2

Hi @RAHUL_singh1 and welcome to the community!

I would be interested to see the calculated field you are using to build the percent change field. If your earning is 100 and the difference in earnings is -25, that makes me think the previous earning was 125. If that is the case, you would want to run the calculation differently.

%Change = ({Earning}/({Earning}-{Difference in Earning}))-1

Since your table is already partitioning your data, this should manage the percent by group and then show the total percent change rather than summing up the values for each group. I believe this would provide your expected output. Thank you!

3

Hi @duncan , Thanks for your response. Let me look try your solution.
correct pervious earning was 125. see below
I am using formula to calculate the %change from last period= {Difference in Earning}/Earning

image
image1902Ă—888 109 KB

4

Hi @duncan ,
No, it did not work. % Total change should be .23 (-70/300) , but I am getting .57

image
image1849Ă—891 127 KB

5

Any thought on above issue ?
Thanks in advance!

6

Hi @RAHUL_singh1,

Have you considered using the periodOverPeriod…Difference functions?
periodOverPeriodDifference
periodOverPeriodPercentDifference

For example I’ve built a similar pivot table using different data below using calculated fields to calculate the differences between years:

Difference in Profit
periodOverPeriodDifference(sum(Profit), {Order Date}, YEAR, 1)

% Difference in Profit
periodOverPeriodPercentDifference(sum(Profit), {Order Date}, YEAR, 1)

image
image992Ă—494 62.6 KB

Could you use these functions instead and achieve the desired result?

Many Thanks,
Andrew

7

Hello @RAHUL_singh1 !

Were you able to try @abacon 's solution above?

8

HI @duncan /@abacon , I am sorry for the late response! Yes, @abacon’s solution gave me the clue to use the sum function. I used following and it worked
%Change = (sum({Earning})-sum({Difference in Earning}))/sum({Earning})

Thanks again for your help!

2 Likes