Forum Discussion

davidwsw's avatar
davidwsw
Frequent Visitor
9 years ago
Solved

Need help filtering a radar chart

I have a simple table where users have scores for different categories. There are 5 categories in total but some users don't have a score in certain categories. For example, Paul has a score in categories A, B and C but not in D and E.

 

 

I want to calculate the average score across all users for each category, ignoring any filters applied to User, so I created this measure:

CategoryScore = CALCULATE ( AVERAGE ( Table1[Score] ), ALL (Table1[User] ) )

 

I then want to show each user's scores on a radar chart and compare them to the average in each category. When no user is selected, it looks like this as expected (both polygons are on top of each other):

 

 

This part is tricky to explain; I'm going to try my best. I want to make it so that when I filter by a user, his polygon is superimposed on top of the polygon that represents the average scores in all categories. For example, if I select John, I want to see B-C-D-E on top of A-B-C-D-E shown above. The problem is that when I filter by John, CategoryScore also gets filtered by the categories that John has. The end result is this:

 

 

 

Is there any way to get the result that I am looking for? Any help would be greatly appreciated.

 

 

 

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    davidwsw

     

    You can create another User table with following formula. And create relationship between Table1 and Table2 with User key but inactive the relationship.

    Table2 = 
VALUES ( Table1[User] )

     

    Then create a Measure with following formula.

    AverageScore = 
CALCULATE (
    AVERAGE ( Table1[Score] ),
    USERELATIONSHIP ( Table1[User], Table2[User] )
)

    For the Slicer visual, select Table2[User] in the Field. Then the selection in Slicer will not filter the category in the Rader chart.

     

    Best Regards,

    Herbert

2 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    davidwsw

     

    You can create another User table with following formula. And create relationship between Table1 and Table2 with User key but inactive the relationship.

    Table2 = 
VALUES ( Table1[User] )

     

    Then create a Measure with following formula.

    AverageScore = 
CALCULATE (
    AVERAGE ( Table1[Score] ),
    USERELATIONSHIP ( Table1[User], Table2[User] )
)

    For the Slicer visual, select Table2[User] in the Field. Then the selection in Slicer will not filter the category in the Rader chart.

     

    Best Regards,

    Herbert