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.
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.
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.
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.
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.
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)
