# Calculating Diff in Pivot Table

**URL:** <https://community.amazonquick.com/t/calculating-diff-in-pivot-table/539>\
**Category:** Q&A\
**Tags:** calculations, formatting, quick-sight\
**Created:** [November 1, 2021, 8:48pm UTC](https://community.amazonquick.com/t/calculating-diff-in-pivot-table/539 "2021-11-01T20:48:06Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jianxiu](https://avatars.discourse-cdn.com/v4/letter/j/f9ae1b/32.png) [@jianxiu](https://community.amazonquick.com/u/jianxiu)\
**Post date:** [November 1, 2021, 8:48pm UTC](https://community.amazonquick.com/t/calculating-diff-in-pivot-table/539/1 "2021-11-01T20:48:06Z")

</div>

Hi,

I have a pivot table with metric fields in “Row” format as “Values” and a date field as “Columns”.  
Sample output would be something like below but without the **{Show different between the 2 dates}**. I would like to see if there’s a way to have **{Show different between the 2 dates}** created. In the underlying dataset, metrics are stored in different fields and each date is stored as a row for the record. “Date1” and “Date2” values are coming from user input.

Name | Metrics | Date1 | Date2 | **{Show different between the 2 dates}**  
John Doe | % of A | 5% | 6% | {+1%}  
| % of B | 3% | 6% | {+3%}  
| % of C | 10% | 5% | {-5%}  
| % of D | 30% | 60% | {+30%}  
| % of E | 3% | 6% | {+3%}  
Mary Smith | % of A | 5% | 6% | {+1%}  
| % of B | 3% | 6% | {+3%}  
| % of C | 10% | 5% | {-5%}  
| % of D | 30% | 60% | {+30%}  
| % of E | 3% | 6% | {+3%}

Thanks!

---

<div class="post-metadata">

**Author:** ![robdhondt](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/robdhondt/32/194_2.png) [@robdhondt](https://community.amazonquick.com/u/robdhondt)\
**Post date:** [November 6, 2021, 1:35pm UTC](https://community.amazonquick.com/t/calculating-diff-in-pivot-table/539/2 "2021-11-06T13:35:56Z")

</div>

Hi @jianxiu -

Here is one approach. The output is slightly different than what you are looking for.

1. Create a calculated field for the percent of metric.

**c\_per\_of\_metric**

```auto
sum(Value)/sumOver(sum(Value),[Metric,Date])

```

1. Create a calculated field for previous month’s **c\_per\_of\_metric**

```auto
windowSum
	(
	     {c_per_of_metric}, 
	     [Date ASC],
	     1,
              0,[Name,Metric]
	) 
	-
	{c_per_of_metric}

```

1. Create a calculated field for the percent difference.

**c\_percent\_difference**

```auto
({c_per_of_metric}-{c_previous_per_of_metric})/{c_previous_per_of_metric}

```

You’ll get a visual that allows you to compare the two dates but also more than 2.

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/1X/ed77de2fcf176a14365ccecadb5b2c8c490214d8.png)

---

<div class="post-metadata">

**Author:** ![kaare](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/kaare/32/8618_2.png) [@kaare](https://community.amazonquick.com/u/kaare)\
**Post date:** [May 1, 2023, 7:50am UTC](https://community.amazonquick.com/t/calculating-diff-in-pivot-table/539/3 "2023-05-01T07:50:35Z")

</div>

@robdhondt While appreciating the suggestion with the percent of metric calculation, I still believe there is a much simpler answer to the initial question being posted here. The point I want to make is that even the question contains the notion of percentage, it is not really about percentages at all. To be precise, what is asked for is a calculation which can display the change in any measure, here the measure is about percentages, but it might as well have been change in the numer of fruits, or cars.

Consider the following dataset:

Name,col1,col2  
John Doe,3,6  
John Doe,10,5  
John Doe,30,60  
John Doe,47,2  
Mary Smith,10,9  
Mary Smith,1,11  
Mary Smith,12,9  
Mary Smith,12,17  
Mary Smith,99,88

and this calculation:

change = col2-col1

Displaying this as a table, yields this:

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/5/5dad164e5a6105bac172a0374e68c72bfb9c74cf.png)

---

<div class="post-metadata">

**Author:** ![Farzana\_Mohammed](https://avatars.discourse-cdn.com/v4/letter/f/5e9695/32.png) [@Farzana\_Mohammed](https://community.amazonquick.com/u/Farzana_Mohammed)\
**Post date:** [May 24, 2023, 2:32pm UTC](https://community.amazonquick.com/t/calculating-diff-in-pivot-table/539/4 "2023-05-24T14:32:48Z")

</div>

How to calculate the difference in this scenario col1 and col2 are in one column but its split based on name while using Pivot?
