Getting average of other similar entries based on drilled through target

[Anonymous]

I am looking to make a chart that compares a given locations emission values to other similar locations, where if I drill-through to the page on a given location, it will then have the chart show it's emissions as well as the average for all locations that match the same subtype, date range, and some other information:

Facility	Province	Subtype	Date	Emissions
Facility A	AB	1	Jan-21	10
Facility A	AB	1	Feb-21	20
Facility A	AB	1	Mar-21	20
Facility A	AB	1	Apr-21	10
Facility A	AB	1	May-21	40
Facility A	AB	1	Jun-21	10
Facility A	AB	1	Jul-21	20
Facility A	AB	1	Aug-21	20
Facility B	AB	2	Jan-21	20
Facility B	AB	2	Feb-21	30
Facility B	AB	2	Mar-21	30
Facility B	AB	2	Apr-21	50
Facility B	AB	2	May-21	40
Facility C	SK	2	Jan-21	60
Facility C	SK	2	Feb-21	50
Facility C	SK	2	Mar-21	60
Facility C	SK	2	Apr-21	40
Facility C	SK	2	May-21	40
Facility C	SK	2	Jun-21	60
Facility C	SK	2	Jul-21	80
Facility D	SK	2	Jan-21	50
Facility D	SK	2	Feb-21	70
Facility D	SK	2	Mar-21	60
Facility D	SK	2	Apr-21	60
Facility E	AB	1	Jan-21	20
Facility E	AB	1	Feb-21	10
Facility E	AB	1	Mar-21	10
Facility E	AB	1	Apr-21	20
Facility E	AB	1	May-21	10
Facility F	AB	1	Jan-21	50
Facility F	AB	1	Feb-21	10
Facility F	AB	1	Mar-21	30
Facility F	AB	1	Apr-21	30
Facility F	AB	1	May-21	40
Facility F	AB	1	Jun-21	80

So essentially, if I drilled into this page on Facility A, I would see it's average emissions (18.75) compared to the average of all facilities in AB with type 1 (24.211)

Whereas if I drilled into facility F I would see it's average (40) compared to the same 24.211

Drilling into C i would have it's average compared to other SK locations of type 2, etc etc.

I would like to be able to visualize this in maybe some different ways, such as selecting a location owner and seeing all of their locations compared to their respective averages at once, so maybe just creating a column in the table with this average would be the best approach

[v-binbinyu-msft]

Hi @Anonymous ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu

[Anonymous]

I forgot to mention, I would like to be able to have this average change based on the date range I am trying to view, i.e able to select Facility A during January 2021 and seeing it compared to the average of other locations during January 2021 only, or selecting a full year or all-time etc, and seeing only the average over that time period