Compare an individual filtered by slicer to average of the category

[kman42]

I have a table of people of various titles and I'd like to compare an individual's performance over time to the average of everyone else with their same title. I'm using a slicer to select the person of interest and am using a line chart to show their performance over time, which works as expected, but I can't figure out the measure to get the average of everyone else in their title (preferably without the person of interest, but I'll take the overall average if I have to). For example

Person table:

Person ID	Name	Title
1	Bob	Assistant
2	Sally	Assistant
3	Mike	Assistant
4	Jen	Associate
5	Phil	Senior

Performance table:

Person ID	Date	Value
1	1/1/2024	2
1	2/1/2024	4
1	3/1/2024	6
2	1/1/2024	1
2	2/1/2024	1
2	3/1/2024	1
3	1/1/2024	6
3	2/1/2024	5
3	3/1/2024	7

If I select Bob with my slicer, then I would like to graph Bob and the average of the other Assistants vs time, but I can't figure out the correct measure for getting the average of the other people of the same title. Any help would be appreciated.

Thanks!

[hansontm · Accepted solution]

Hi there, I created the following measure:

Avg Value =

VAR selectedtitle = SELECTEDVALUE(Person[Title])

RETURN CALCULATE(AVERAGE(Performance[Value]),  Person[Title] = selectedtitle, ALL(Performance))

This does not filter out the selected person, but provides what you asked for. 

If correct, please mark as solved.

[Anonymous · Accepted solution]

Hi @kman42 ,

Based on the description, the method hansontm provided should be helpful.

Besides, you can try the following DAX formula. Creating two measures to calculate the individual average and others average.

Average Performance = AVERAGE('Performance table'[Value])

Average Performance Others = 
VAR SelectedPerson = SELECTEDVALUE('Performance table'[Person ID])
VAR SelectedTitle = CALCULATE(MAX('Person table'[Title]), 'Performance table'[Person ID] = SelectedPerson)
RETURN
CALCULATE(
    AVERAGE('Performance table'[Value]),
    FILTER(
        ALL('Person table'),
        'Person table'[Title] = SelectedTitle && 'Person table'[Person ID] <> SelectedPerson
    )
)

Then, drag the Date column to the X-axis and drag the two measures to the Y-axis.

Selecting the Bob and the line chart is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[hansontm · Accepted solution]

Hi there, I created the following measure:

Avg Value =

VAR selectedtitle = SELECTEDVALUE(Person[Title])

RETURN CALCULATE(AVERAGE(Performance[Value]),  Person[Title] = selectedtitle, ALL(Performance))

This does not filter out the selected person, but provides what you asked for. 

If correct, please mark as solved.