Rank by Average Score in Date Range for Individual in Group

[Anonymous]

Hello,

I want to create a measure to get a dynamic individual rank by group based on date range selected.  The data table rows include: Date, Name, Group, and Score (among other things). I have slicers on the visual that allow for single selecting a Name, and one for selecting multiple dates. 

My goal is to be able to slice down to an individual and have a measure calculate the rank of the individual within their group based on an average of their scores as compared to an average of the scores for each of their peers in the same Group over the selected time period.

Example Table:

Date	Name	Group	Score
January	Bob	A	110%
January	John	A	85%
January	Carol	B	120%
February	Bob	A	60%
February	John	A	100%
February	Carol	B	110%

Carol should never be included in ranking against Bob and John because she is in a different Group.

If both Dates are selected,

          Bob’s average Score = (110+60)/2 = 85%

          John’s average Score = (85+100)/2 = 92.5%

          Carol’s average Score = (120+110)/2 = 115%

John should be ranked #1 and Bob should be ranked #2 for the sliced Dates.  Carol should be ranked #1 (based on her own group).

Now, if I slice down by Name to just see Bob’s information for that date range, his rank still needs to display as #2.

However, if the Date range is changed, and only January is selected,

          Bob’s average Score = 110/1 = 110%

          John’s average Score = 85/1 = 85%

Bob should now be ranked as #1.

I’d appreciate any assistance you could offer here.  I've tried several things and had no luck.  Thanks in advance!

[parry2k]

@Anonymous these measure will do it:

Avg = AVERAGE( Table1[Score] )

Rank = RANKX( FILTER( ALL( Table1[Group], Table1[Name] ), Table1[Group] = MAX(Table1[Group] ) ), [Avg] , , DESC )

Add a table visual, put Group, Name, Avg and Rank on columns and you will get the result as expected.