Weighted Average Between Multiple Values

Question & Answer
, ,
1

Hello Everyone!

Thank you in advanced for your help. I am creating a analysis to evaluate the Search Engine Results for multiple of our marketing accounts. I am trying to find the weighted average for a SERP position.

I am currently using the following calculated field to find the weighted total average for SERP position:
(sum(Impressions * Position)) / TotalImpressions

I’ve attached a picture of a table that shows accounts (blanked out) and a column for Current Weighted Average Position.

I would like to find the amount of accounts that have a distribution for weighted average position between 1-3, 4-10, 11-20 and 21+. When I attempt to use this calculated field in my filters (based on the table, there should be 3 accounts with avg position between 1-3.99)
image

I am using the calculated field as a filter value to bring in anything with a position between 1-3.99, but my KPI is saying no data even though it should count 3 accounts. The table and KPIs are using the same calculated field.

2

Hi @emilie_lujan, welcome to the QuickSight Community! It appears that your calculated field for the weighted average SERP position is correct, but maybe the problem lies in the application of filters and ensuring the KPIs and table use the same logic.

Here’s one approach on how to properly configure your filters and ensure accurate KPI results:

Step-by-Step Solution:

  1. Verify Calculated Field: Ensure your calculated field for the weighted average SERP position is correct:

Pseudocode (Syntax may vary)

(sum(Impressions * Position)) / sum(Impressions)

This should give you the weighted average position.
2. Create Binned Categories: Create a new calculated field to categorize the accounts into bins:

Pseudocode (Syntax may vary)

ifelse(
    {Weighted Average Position} <= 3.99, '1-3.99',
    {Weighted Average Position} <= 10, '4-10',
    {Weighted Average Position} <= 20, '11-20',
    '21+'
)

Replace {Weighted Average Position} with the actual field name for the weighted average position.

  1. Apply Filters: Ensure the same calculated field is used consistently in both your table and KPIs.
  • For the table, apply the calculated field directly.
  • For the KPI, set up the filters to count the number of accounts in each bin.
  1. Count Accounts in Each Bin: Create a calculated field to count accounts:

Pseudocode (Syntax may vary)

countDistinct({Account ID})

Replace {Account ID} with the unique identifier for your accounts.
5. Set Up KPI:

  • Create a separate KPI for each bin.
  • Apply a filter to each KPI to count the accounts in the corresponding bin.

Example Configuration:

  1. Weighted Average Position:

Pseudocode (Syntax may vary)

(sum(Impressions * Position)) / sum(Impressions)
  1. Position Bins:

Pseudocode (Syntax may vary)

ifelse(
    {Weighted Average Position} <= 3.99, '1-3.99',
    {Weighted Average Position} <= 10, '4-10',
    {Weighted Average Position} <= 20, '11-20',
    '21+'
)
  1. Count of Accounts:

Pseudocode (Syntax may vary)

countDistinct({Account ID})
  1. Filters for KPIs:
  • KPI 1-3.99: Filter Position Bins = ‘1-3.99’
  • KPI 4-10: Filter Position Bins = ‘4-10’
  • KPI 11-20: Filter Position Bins = ‘11-20’
  • KPI 21+: Filter Position Bins = ‘21+’

Common Issues to Check:

  • Ensure Consistency: Ensure the calculated fields are consistently applied across your analysis.
  • Aggregation Level: Make sure you are aggregating at the correct level (e.g., account level) in both the table and the KPIs.
  • Data Type: Verify that the calculated fields are of the correct data type and format.

By following these steps, you should be able to accurately filter and display the weighted average positions and count the accounts within each specified range.

Here is a good article on level-aware calculations:

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)

1 Like