Hello @Ganga, welcome to the Quick Sight 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!