Forum Discussion

Ania_Learn's avatar
Ania_Learn
Frequent Visitor
6 years ago
Solved

How to create a table using another without losing filtering links

Hello,

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

 

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

SectorsVariable 1Varriable 2 Variable 3
Sector1224310
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 1Sector 2Sector 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

 Sector1Sector2Sector3
Maximum of the average of (variable 1);average of (variable 2)565656
Minimum of the average of (variable 1);average of (variable 2)101010
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 !

 

 

 

Need Help
Tips and Tricks
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

     

1 Reply

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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.