Displaying the Average Per Type of a Non-Sliced Column

[PowerbyEye]

Hi all, I was wondering if anyone may be able to help with a solution to the following:

We have a source data table that has a list of locations, that location's 'Location Type', and that location's score against a series of metrics. A simplified example of what this data table looks like is as follows:

In PowerBI, we would like the user to be able to select a location number from a slicer and to see that location's score against each metric, compared to the Average Figure for that location's location type. A simplified example of the desired result looks something like this:

We have tried using a Matrix visual, and various different approaches for for getting these figures side by side. We have tried using measures and calculated columns to acheive this, but are struggling to solve this. When we have a calculated column, we are struggling to get this presented side by side in a matrix!

If anyone may be able to help us with a solution to this, it would be very much appreciated!

[Data-estDog · Accepted solution]

Step 1: Unpivot your data

should look like this: 

Step 2: You need 3 measures:

Step 3: Add fields to matrix

You can then rename the fields in the "Values" section of above. SumOfMetric will always be the values for the chosen location.

Step 4: Mark this post as the solution and give Kudos if you feel so inclined. 

[PowerbyEye]

Thank you very much, this looks like it might be the one! We'll give it a try and confirm!!

[Data-estDog]

Sure. Some minor things. Turn off column and row sums on matrix. 

Formatting of values to reflect %, $, etc. will be another hurdle. 

[PowerbyEye]

We can confirm that this is the solution - thank you again very much for the help! We have indeed now hit those %, $ etc. hurdles and will have a go at trying to sort them. Thanks!