How to create a table using another without losing filtering links

[Ania_Learn]

Hello,

Please i need help, i spent ALL day trying to find solutions ....

I did a report using a table that  looks like this :

Sectors	Variable 1	Varriable 2	Variable 3
Sector1	22	43	10
Sector2	..	..	..
Sector2	...	..	..
Sector2	..	..	..
Sector3	...	..	..
Sector1
Sector3

But i want to transform the table to generate another type of visuals using R script (WITHOUT losing my report visuals that were done with the first table, and i want to keep the link between the two tables. Meaning that if i use a slicer from my original table i  want it to impact the 'table i want to generate'

The 'table i want to generate' : 

	Sector 1	Sector 2	Sector 3
average of (variable 1)	... 20 for examples	...10	...56
average of (variable 2)	..10	...41	...32

And then i want to add two lines to this table as the first rows

	Sector1	Sector2	Sector3
Maximum of the average of (variable 1);average of (variable 2)	56	56	56
Minimum of the average of (variable 1);average of (variable 2)	10	10	10
average of (variable 1)	... 20 for examples	...10	...56
average of (variable 2)	..10	...41	...32

What i did : i just went to the query editor and copy/past my orginial table. I deleted the variable 3 (cuz i dont want) and after constructing the table i wanted with R script (3rd one here), i have generated my visual. PROBLEM => My visual don't take into account the slicer modifications of variable 3. Because it a new table without variable 3 ...And i cant keep variable 3 in my new desired table because the R visual need exactly the last structure to be run.

Please i need help !

[v-lionel-msft · Accepted solution]

Hi @Ania_Learn ,

Please create a matrix like this.

Avg_V1 = 
VAR x = 
CALCULATE(
    AVERAGE(Sheet1[Variable1] ),
    ALLEXCEPT( Sheet1, Sheet1[Sectors] )
)
RETURN
x

Max_avg_V1 = MAXX( ALL(Sheet1), [Avg_V1] )

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.