Looking to graph a filtered subset of data against a broader set of the same data for comparability

Hi, I have a dataset containing daily cost, sales, and margin data as well as an associated region and location field. I’m looking to set up a line graph where I can show the daily margin of a specific location compared to the region it falls under. I initially used a few “sumOver” and “sumif” (edited) functions to create new fields that essentially filter the data before graphing, but I am hoping to do this for a variety of metrics and differently based on the location being reviewed, so I am looking for a solution that wont litter the dataset with calculated fields - is there a better way to go about this? Thank you in advance.

Hello @Jon.Collins , welcome to the Quicksight community!

I think what you have described will be the best way to accomplish this, but you could include parameter control filters on the sheet and calculated fields so that you user only has to change one thing and it will affect both lines.

What I did was created a Region and Country parameter controls that were cascading based on hierarchy. I then created two calculated fields like the one below for each:

ifelse(
{Region} = ${REGION},
{Sales},
NULL
)

What I ended up with was a dynamic line graph that would should sales by region and by country.

Let me know if this helps or if you have any questions!

Hello @Jon.Collins !

Were you able to try what I suggested above, and if it worked could you mark the comment as a solution to help the community?