Calculate average with condition

Hello team,

My dataset has the following column:
Product - Product Attribute 1 - Product Attribute 2- Month of Sales - Sales Amount

How to create calculated columns that:

  • shows the average sales of the first 3 months for each product?
  • show the average of the average sales of the first 3 months of all products with the same attribute 1 and attribute 2 (need to be the same attributes). So this number is the average of the above calculation but for more than 1 product.

In these two calculations, the month of sales must be [1,2,3], if there is no 1,2,3 in the Month of Sales, return an empty space.

Note that I need to show these two columns in a table or pivot table.

Please also suggest if this should be done as SQL in data prep to have better performance.

Thank you very much!

Hi @Bunnydata to that you can try:

Select “Add Calculation” from the dropdown menu.

Enter a name for the calculated field (e.g. “Avg Sales First 3 Months”).

Use the following formula:

IF(OR({Month of Sales}=1,{Month of Sales}=2,{Month of Sales}=3),AVG({Sales Amount}) OVER (PARTITION BY {Product} ORDER BY {Month of Sales} ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), “”)

This will create a new calculated field that shows the average sales of the first 3 months for each product. If there are no sales in the first 3 months, an empty space will be shown

Average of the average sales of the first 3 months of all products with the same attribute 1 and attribute 2:

Use the following formula:

IF(OR({Month of Sales}=1,{Month of Sales}=2,{Month of Sales}=3),AVG(IF(OR({Month of Sales}=1,{Month of Sales}=2,{Month of Sales}=3),AVG({Sales Amount}) OVER (PARTITION BY {Product} ORDER BY {Month of Sales} ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), “”)) OVER (PARTITION BY {Product Attribute 1}, {Product Attribute 2} ORDER BY {Month of Sales} ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), “”)

This will create a new calculated field that shows the average of the average sales of the first 3 months of all products with the same attribute 1 and attribute 2. If there are no sales in the first 3 months, an empty space will be shown.

As for whether to do this as SQL in data prep for better performance, it depends on the size of your dataset and the complexity of your calculations. If your dataset is large and your calculations are complex, it may be more efficient to do this as SQL in data prep. You can use QuickSight’s SQL editor or connect to your data source using Amazon Athena or Amazon Redshift to write the SQL query

Hope this helps

1 Like

Hi Blender, @Blenner

Thank you for your help. I feel like living in a different universe because my current Quicksight account does not even have any syntax like “IF” (only ifelse) or they way you use OR is not acceptable there. Do you think I may use a different of Quicksight?

Hello @Bunnydata how are you?

you can use “ifelse” instead of “IF’s” it also works just at the end of each logic we have “else” using the comma (,“” )

1 Like