Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |