Average score of specific category based on the selected customer

[jiri81]

Hello all,

I have a  multi-page report that allows my colleagues to search for a customer (based on the customer's name). The report displays various parameters related to this particular account (stuff going in/out/backlog/customer surveys etc).  I have also created a  scoring formula that considers various parameters and displays a score in %. To complete this, I would like to display a benchmark of how all the customers of a particular category are doing.  Let's assume this is an average.

And here comes the struggle - I cannot approach this in a simple way that will allow me to display the benchmark only for this category. Let's use this example:

My data:

customer name	customer level	score
a	gold	10
b	platinum	20
c	premium	15
d	silver	1
e	silver	2
f	silver	0
g	gold	3

I would like to use a slicer to select a particular customer -  let's say customer "a" is selected. Customer  "a" is category gold.

I would like to display  

Customer A score - 10

Customer A level -  gold

Average Gold category score -  6,5

In reality, I have the data in multiple tables, and the score calculation is more complex .. but I can't figure out the basics here.

Is anyone able to share a sample or kick me the right way?

Thank you!

Jiri

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

[jiri81]

Thank you, Jihwan. 

The attached pbix is working fine. I have migrated the solution over to my production dataset  but currently having some issues - the visual takes a long time to load and often ends up with an error

"This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.

Please try again later or contact support. If you contact support, please provide these details...."

The fact is that I have ~700 000 lines in the data table and another 3000 entries in the customer database.  The score is also a set of calculations.

I'll give it another try tomorrow.