LAC for column averages

[jemin]

Hi,

I have two views,
first view has

topics	% values	customers	customers_all
A	31.53%	19.27M	61.1M
B	2.37%	1.3M	54.38M
C	…	…	…
D	…	…	…

% values are calculated as sum(customers) / sum(customers_all)

Now, for View 2 I just want the averages of the topics filtered from view 1 (say topic A & B)

so, I used for customers avg –
sum({customers})/distinct_count({topics}) – This works perfect

But the average for % calculations gives weighted average instead of true average.

calc field – sumOver({% values })

true average should be (31.53+2.37)/2 = 16.95%
weighted average = (19.27 + 1.3)/(61.1 + 54.38) = 17.80%

also, tried sumOver({% values}, [topics]) but this gives missing references in the view because topic field is not included in view 2.

so, how do I get the actual average?

[David_Wong]

Hi @jemin,

Can you try one of these? I think both should work.

min(sum({customers}, [])) / distinct_count({topics})

min(sumOver({customers}, [], PRE_AGG)) / distinct_count({topics})

[jemin]

Hi @David_Wong ,

Thank you for responding. I think you got confused bc of so much information I put in the question. Apologies for that.

I was able to get the average for customers just fine. The issue I’m facing is for the average of % values. In that I want true average but I’m getting weighted average in quicksight.

[David_Wong]

Hi @jemin,

If you change your calculated field for % values to the following, it should work:

avg(sum(customers, [topics])/sum(customers_all, [topics])) 

You can use the same calculated field in both views. When you use it in your 2nd view and remove the topics column from your table, it will give you the correct average.

[jemin]

Thanks again for the response @David_Wong ,

I tried your approach and am getting “LAC aggregate functions inside one visual aggregate functions should always share the same grouping key” error in both views.
Whether I include topics in the group by or not.

I read through some of your past answers regarding this issue but not relates to my use case… Appreciate your responses here.

[David_Wong]

It works in my test.

Test dataset:

View 1:

View 2:

Using the same calculated field, I can also add a total row to View 1 to get the same result:

Are you not grouping by the topics field in both sums? Is customers_all a calculated field? I didn’t see the calculation for it in your question, so I assumed that it’s not calculated.

[jemin]

Hey @David_Wong ,

It worked for me now. I realized that calculation you provided was correct, the last same grouping key error I was getting is because “Topics” was an if else string manipulation calculation.

Which is very strange, I wasn’t changing anything just adding a key word in the same string… but any way when I used the base topics field in the calculation it worked out.

Thanks so much for your support!

[jemin]

Hey @David_Wong ,

Since the question is still open, can I ask a quick follow up?
I created the views and calculations work fine, now I’m trying to use the “% values” calculated field as a filter.

say filter only for topics with % values greater than or equal to 50%. The filter works fine for view 1 but filters everything out for the view 2 (the averages one) because the average of % values field is less than 50%

Is there a way I can have this filter that it just filters for topics in view 1 and in view 2 I get the averages of topics left in view 1??

[David_Wong]

Hi @jemin,

If you want to add a filter, you have to use LAC-W instead of LAC-A. Keep the previous calculated field to show in the visuals but create another one using LAC-W and use it for filtering purposes.

% values (LAC-W):

sumOver(customers, [topic], PRE_AGG) / sumOver({customers_all}, [topic], PRE_AGG)

Using LAC-W functions for filtering is covered in use case #3 in this article:

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

[jemin]

@David_Wong

sumOver(customers, [topic], PRE_AGG) / sumOver({customers_all}, [topic], PRE_AGG)

Hey @David_Wong ,

Thanks for the response. I tried that approach and it still filters out everything in view 2… the averages view.
Here

Used the same sumOver calculation you mentioned and added it as filter.

[David_Wong]

Hi @jemin,

You need to select “No aggregation” in the Aggregation dropdown list when creating the filter. Can you try that?

[jemin]

That worked! Thanks so much @David_Wong .

Could you help explain why it worked as No aggregation and not with sum or average?? when the intention is sum of the field to >= filter value.

[David_Wong]

Create a table visual containing the following columns:

• topics
• customers
• customers_all
• The calculated field which contains the LAC-W function.

It will help you understand how LAC-W functions work.

You’ll see that the same percentage is displayed in every row that contains the same topic because we used topic as our partition in the sumOver function.

If you select average as aggregation, QuickSight will calculate the average value as (60 + 60 + 31.03 + 31.03 + 31.03)/5 = 42.62 since you’re not grouping by anything in view #2. If you then filter by >= 50, you won’t get anything.

If you don’t select any aggregation and filter by >= 50, it will exclude topic B and your calculation for the percentage will be based on topic A only.

[jemin]

This is super helpful. Thank you so much @David_Wong .

I’ve marked the issue as resolved.