Hello,
I would like to know if there is any workaround in displaying the values from the same data source or any other work around.
Based on row level, at a given point of time, a user can access only to a single Region and cant access other regions data. But I need to consider other Regions data for 2 metrics as mentioned..
Common filters are Region, City, Category & Name. Lets say X user belongs to Region-1 and Region filter will show only Region-1 value.
Sample Data:

I would like to create 2 Calc fields at month level i.e. PB & NB
Scenario 1: Default selection of all quick filters..
Expected/Logic for PB:
Calculate the Revenue for each month who belongs to region-1.
i.e. 310 = F2+F3; 400=F4; 0= No values for Mar in Region-1; and so on...

Expected/Logic for NB:
Calculate the Revenue & Qty for each month for all regions i.e. Region-1&2..( Even though user belongs to Region-1, in calculating NB, we need to consider all regions from the table)
i.e. Rev 615 = F2+F3+F12 ; 750=F4+F13
Qty 44 = G2+G3+G12 ;23 = G4+G13
Result 13.977 = 615/44 and so on.....

PB & NB results should not vary for City, Name quick filters changes. It should be constant, but should vary for Category only.
Scenario 2:
when Category=Coloring is selected then below results should be displayed.


Scenario 3:
when Category=Others is selected then below results should be displayed.


Appreciate it!
Thanks!