Comparing Values and scorecards based on a lookup table

[jvirgi]

I have 2 tables of data (see below example), 1 with a listing of a bunch of reviews on sports with a star rating.  I have another table thats a reference that says what sport i should benchmark it against.  In other words, what i want to compare that selected sport to.  

I would also have a slicer that allows me to choose a sport of choice.  I would prefer to use the reference table as my slicer.

Question 1: How do I make a chart of average Star Rating that ONLY shows the chosen sport and its Benchmark? It would look something like this:

Question 2: How do I make a scorecard showing each sport compared to its Benchmark? For example, below and red or green is calculating by comparing star rating vs the benchmark's star rating. It would look something like this:  

Sport	Rating
Hockey	3
Hockey	4
Hockey	4
Football	5
Golf	2
Golf	1
Soccer	5
Soccer	5
Soccer	5

Main Sport	Benchmark
Hockey	Football
Golf	Soccer
Soccer	Golf
Football	Hockey

[v-gizhi-msft · Accepted solution]

Hi,

Hope this can help:

Here is my test pbix file:

pbix 

Best Regards,

Giotto

[jvirgi]

@v-gizhi-msft Thanks!!  This works well and what I was looking for.

Is there a way to show the red / green chart to just be indiviual card visuals or single colored boxes for each sport? I know i could create a bunch of slicers only linked to each visual but that seems very messy.  I was thinking can i make a card that just links to the first sport, another for the 2nd, and so on.

[Greg_Deckler]

Perhasp if you did this:

Table1 * <- 1 Table2 (get rid of your benchmark column) 1 <-> * Table3

So your Table1 and Table3 as presented below minus the Benchmark column in Table2

Create a third table:

Sport,Benchmark

Football,Football

Hockey,Football

Football,Hockey

Hockey,Hockey

...

Use Benchmark for your slicer. Use Table2 sport or Table3 Sport for you axis.

Might work.

[jvirgi]

Thanks @Greg_Deckler.

I will try it but I’m guessing when I slice by benchmark I’ll only get the 1 sport, rather than both the benchmark and main sport.