Aggregation at Higher granularity level is not working properly while applying filters

Question & Answer
, , , ,
1

I have a line chart which shows avg of product sales. And now the requirement is I need to add multiple aggregations in to the existing line chart. Ie, 2 more lines, one is aggregated at state level and another is aggregated at region level. Existing line is the one with Lowest granularity (Store ) level. So the expectation is whenever we select a particular store, store level line shows the avg value of the metric for the selected store. State level aggregation should shows the aggregated value of the metric for the state in which the store resides . Similarly region level aggregation should show the aggregated value of the region in which the particular store resides.
When all the stores are selected, then all the 3 levels should display same value.
I have done the following calculations to achieve this.

image
image1399×783 70.7 KB

Avg Product Sales (Which is the lowest granularity level) = avg({product_sales})

State Level-LAW = avgOver ( ({product_sales}), [{region_name},{state_name},{application_month_dt},{product_category},{product_sales}],PRE_FILTER )

Region level-LAW = avgOver((product_sales}),[{region_name},{application_month_dt},{product_category},{product_sales}],
PRE_FILTER )

Whenever I select a particular store, Avg product sales should show the avg product sales value of that store and State level should show the avg product sales value of the state in which the store resides and region should show region’s product sales value in which the selected store resides.
But here the issue is whenever I select any store, all the aggregations are changing to show the value of the selected store. Ie, all the lines are appearing as a single line as shown below. But it should not appear like this. It have to appear as 3 different lines.

Can anyone help to solve this issue, if there is anything wrong with the calculation or process I am doing?

1 Like
2

Hello @Ganga, welcome to the QuickSight community!

So there are a few things going on here. Likely, what we will need to do, is use the parameters connected to your filters in a calculated field rather than directly filtering the visual. Also, to confirm, you want the store selected (lowest level granularity) to determine all 3 values?

Basically, it seems like we will need to create calculated fields to check which value we want to return for Region and State if a Store is selected.

FilteredRegion = ifelse(
${Store No} = {Store No}, {Region},
NULL
)

This same calculation can also be run to return Stores and States selected.

Now, you want to create calculated fields for each average:
StoreAvg = avgOver({product_sales}, [{Filtered Store}, {application_month_dt}], PRE_AGG)
StateAvg = avgOver({product_sales}, [{Filtered State}, {application_month_dt}], PRE_AGG)
RegionAvg = avgOver({product_sales}, [{Filtered Region}, {application_month_dt}], PRE_AGG)

Last, we will check check if no store is selected, and we will either return our calculated field or a field that checks average for everything.

ifelse(isNull(${Store No}), avgOver({product_sales}, [{application_month_dt}], PRE_AGG), {Store Avg})

That last calculation will need to be repeated for all 3 of your options. Then you will add those calculated fields into your visual and remove filtering based on store. I’ll mark this as the solution, but if you have any follow-up questions, please let me know!

3

Hi @DylanM ,

Thank You So Much for the Suggestions!!

Here in this solution all the calculations are based on single select filters right?. what if I need to select multiple stores, States, Regions ? I tried this approach, but still i am facing the same issue…also multiple selection of stores should return the aggregated value of all the states wherever the selected stores are residing in. I missed to mention that point, like it was expected to be multi select. Could you please help on this?

4

Hello @Ganga, okay, I see. So utilizing multi value parameters will greatly reduce the functionality for them and they can no longer be utilized in calculated fields.

Due to this, I think it is likely that you will need to alter your expectations for this visual. The issue here is that you will be unable to exclude a filter from some values in the visual. Whenever you select values for Store No., it will alter the values displayed for the State and Region level. I think we can work out an alternative solution for you, but I don’t see a path forward with the current expected result.

5

Hello @Ganga, since we have not heard back, I will mark my above response as the solution. If you still need assistance with this issue in QuickSight, please let me know.