Constant Reference line not impacted by filters

Question & Answer
, ,
2

Hi @mudit92, to achieve the desired reference line behavior in QuickSight, you will need to create a calculated field that isolates the median value for the selected marketplace and date range, independent of the selected sellers.

Here is one approach on how to do this:

  1. Create a Calculated Field for the Median Value:
  • Go to your analysis in QuickSight.
  • Click on the dataset used for your analysis.
  • Click on “Add” and then “Add calculated field.”
  • Create a new calculated field to compute the median value for the selected marketplace and date range. Use a formula like the one below:

Since QuickSight at this time does not have a medianOver function, try using avgOver or medianif — there is a feature request here - medianOver() - funtion to be added

AvgOver Function

MedianIf Function

Pseudocode (Syntax may vary)

Instead of medianOver use avgOver or medianIf (
  {seller_score},
  [marketplace, dt>start_date and dt<end_date],
  PRE_FILTER
)

Try using avgOver or medianif functions

Replace seller_score, marketplace, dt, start_date, and end_date with the actual names of your fields and parameters.

  1. Add the Reference Line:
  • Go to your line chart visualization.
  • Click on the pencil icon to edit the visual.
  • Click on “Add” under the “Reference Lines” section.
  • Select “Calculated Field” and choose the median calculated field you created.
  1. Configure Filters Appropriately:
  • Make sure that the filters for the seller ID are not applied to the reference line calculation. This can be achieved by using medianOver or a similar function that aggregates over the specified fields while ignoring others (PRE_AGG), in this case the filter (PRE_FILTER).
  1. Verify the Reference Line:
  • Ensure that the reference line stays constant as you add or remove sellers in the seller ID filter.
  • The reference line should only change when you change the marketplace or the date range.

Also, try using the Amazon Q Assistant on the functions documentation page.

Amazon Q

Build calculations with Generative BI

Another approach is using Generative BI, you can use natural language prompts to create calculated fields within QuickSight, as shown in the following image. For more information about calculated fields in analyses, see Adding calculated fields.

Amazon Q Build Calculation

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)