Forum Discussion

jvirgi's avatar
jvirgi
Icon for Helper III rankHelper III
6 years ago
Solved

Comparing Values and scorecards based on a lookup table

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:  

 

SportRating
Hockey3
Hockey4
Hockey4
Football5
Golf2
Golf1
Soccer5
Soccer5
Soccer5

 

Main SportBenchmark
HockeyFootball
GolfSoccer
SoccerGolf
FootballHockey

4 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support

    Hi,

     

    Hope this can help:

    Here is my test pbix file:

    pbix 

     

    Best Regards,

    Giotto

    • jvirgi's avatar
      jvirgi
      Icon for Helper III rankHelper III

      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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    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's avatar
      jvirgi
      Icon for Helper III rankHelper III
      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.