Calculating Percentage Difference

Hello everyone,

I am looking to create a calculated field to determine the percentage difference of time intervals, count of categories, and average number of products, and then utilize this calculated field in the columns within the Field Wells.

My dataset includes fields such as categories, products, time intervals(seconds), count of categories, average number of products, months, and years.

I aim to compare the time intervals, count of categories, and average number of products between the current and previous years.


**

I am striving to replicate something similar to the image provided above:

**

Your guidance on how to proceed with this would be greatly appreciated.

Thank you for your time and assistance.

Hi @teja1
did you check out

BR

Hi @ErikG ,

I’ve reviewed the document you shared. I’m encountering an issue with the percentDifference() function where I’m not receiving any value. I’m unsure if I’m utilizing the function correctly. Additionally, I want to use this %change field in the Columns in the Field Well. Could you please provide detailed guidance on how to accomplish this?

Here’s the percentDifference() function I’m currently using:

percentDifference(
sum(timeinterval),
[sum(timeinterval) ASC],
1,
[years]
)

I also have a question: Do I need to create separate calculated fields for each time interval, count of categories, and average number of products fields to calculate the percentage difference between the current year and the previous year? If so, how can I replicate something similar to the image provided above:

Looking forward to your guidance. Thank you!

Hi @teja1,

You can get a close enough view using table calcs on your pivot.


You can add the same measure field again and set it to show Percentage Difference table calculation with calculate as set to Group Across to get this effect.

Sample dashboard given below.
CommunityQuestion-26008

Regards,
Arun Santhosh
Pr QuickSight SA

1 Like

Hi @ArunSanthosh, Thank you for your help

1 Like

@ArunSanthosh

I also have similar kind of usecase, I tried calculate as option but if i sort the year in descending then iam not getting % difference for 2024.

and for some of the measure field calculate as option is not enabled. Is there is any other options to overcome such issues.

Hi @sbaskax, I encountered a similar issue recently and found a solution that worked for me. I believe it might help you as well.

I approached it by creating two calculated fields, one for calculating the count of categories for the year 2023 and another for 2024. Then, I created another calculated field to compute the percentage difference between the two.

step 1:

Created calculated for calculating the count of categories for the year 2024

countIf(categories,years=yearparam)

yearparam is a calculated field that extracts the year from a date parameter. For example:

extract('YYYY',${Date})

step 2:

Created calculated for calculating the count of categories for the year 2023

countIf(categories,years=yearparam-1)

step 3:

Percentage difference of both

((currentcategorycount-previouscategorycount)/previouscategorycount)*100

Create a separate visual like a table visual for the percentage differences and attach it

Feel free to give it a try and let me know if you encounter any issues!

This is the output

@teja1 , thank you for sharing these details, this was very much helpful.

This saved me. I didn’t realize I could add a value multiple times :slight_smile: Thanks so much!

1 Like