Getting an average of values with the same types/categories when drilling through to table

[Anonymous]

I have a dataset where a given location id has a multitude of values under different product ID's, each location also has a code based on it's type/region. I would like to be able to drill down on a location id within my table of locations into a bar graph that shows it's value for each product compared to the average values for that product across all other locations with the same type/region.

I would also like this to be filterable by a date range using either a slicer and/or if it was drilled down to a specific year/month.

The goal would be to have a chart that gives something like the following:

but, obviously with the average of type being correct as describe above.

[v-henryk-mstf]

Hi @Anonymous ,

This does not seem difficult to achieve, can you provide the relevant test data to facilitate me to answer for you as soon as possible.

Looking forward to your reply.

Best Regards,
Henry

[Anonymous]

Apologies for not getting back to this sooner, I was away from work temporarily.

Unfortunately due to not only the confidential nature, but also the immense size, of the real dataset I am unable to provide an example dataset, but the relevant fields of the data are as follows:

Production Month, Operator Name, Facility ID, Activity_Product, Volume, Province

I have these relevant fields in a matrix using the following structure:
Rows (this is the correct nesting order, top-to-bottom): Operator Name, Facility ID, Production Month

Columns: Activity_Product

Values: Volume

As a more in depth explanation of the goal:

I would like to be able to drill through into a specific facility or operator to a page where I have a clustered bar graph. It should then show each Activity_Product volume for that selection compared to the average volume for each activity_product of all of the other facilities or operators within the same province (depending on what I've drilled through on). I would also like to be able to have this average excluding the values of 0, since they are irrelevant to the comparison.

I have done all of these comparisons before using Excel, so I understand the actual math, but I would like it implemented in order to allow me to check any given target to averages automatically and I am still rather unfamiliar with the Power BI environment.