Using A Level Aware Caclulation as a Filter

Say I have a table that has “Director | Total Sales” but I only want to include sales for employees that have made a sale in both product category A and product category B.

Of course, if I include the employee in the row level I can create a “Sales in Product Category A” & “Sales in Product Category B” fields and filter on those.

However, if I want to roll up to the Director level, even using a LAC function such as
sum(Sales in Product Category B, [employee]) and using that as a filter, it is not correctly filtering at the employee level.

by including the employee in the row level you mean you pivoted the table by employee?

My recommendation would be pivoting the table, you can use simple SQL or transform that using Glue DataBrew.

1 Like

I mean by including employee in the row dimensions, i’d be easily able to filter to employees that have sum(sales in product A) >0 & sum(sales in product B) > 0.

By rolling the data up, I lose that ability. Looking for the solution in QuickSight oppose to other tools if possible.

@smithjackson35 can you share your sample table and expected output?

1 Like

Hi @smithjackson35

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.

1 Like

It would look something like this. Since John Kennedy and Joe Biden did not both product A & product B, they would not be included in the view rolled up in the top right.


This is not Level Aware Calculation.
I don’t think there is a straightforward solution to your QS problem.
Unless you have a small number of available products and you write complex ifelse, which puts some flags upon having a line item for each product.


1 Like

Got it. For the Level Aware Calc, I was just thinking I could somehow do a sum of sales of the employee, product level to accomplish this. Thanks

1 Like

Hi @smithjackson35 - This can be solved using LAC-W function.
You can calculate the product A and B sales for each employee and have that data available at each detail record level using following calcs.

Employee Level Product A Sales
sumOver(ifelse(Product = 'Product A',{Items Sold},0), [{Employee Name}], PRE_AGG)

Employee Level Product B Sales
sumOver(ifelse(Product = 'Product B',{Items Sold},0), [{Employee Name}],PRE_AGG)

If you want other filters to be ignored in these calcs, use PRE_FILTER as aggregation level and if you need to do the grouping at different grain, adjust the partition attributes accordingly.

Then, just filter out records having 0 value for these calculated fields.
(See screen shot below)

I’m marking this as solution to your question.

Arun Santhosh