Help keeping aggregate measures in a visual. Trying to compare individuals to average by category.

[tortusstorylist]

Hello,

I am trying to create a visual that has individual metrics, as well as the average of those same metrics by employee type. 

As expected, the average measure is showing the average for that individual. What I want to do is to have the static average by "StaffType" in the "BudgetHoursAverage" column, as shown below:

So, I would like to have 31.50 for all individuals who's StaffType is "DIR", etc...

I had created a measure I thought would accomplish this but it is still returning the average by individual:

BudgetHoursAverage =

VAR __table1 = SUMMARIZE('tablename', 'tablename'[StaffType], "name1", SUM('tablename1'[ProdHoursMonth]) / [FTE Total])

RETURN

CALCULATE(AVERAGEX(__table1, [name1]), FILTER('tablename', 'tablename'[StaffType]))

Note, that this is calculating based off of two tables (tablename and tablename1). I can provide more information as needed, as well.

Any help would be greatly appreciated!

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[tortusstorylist]

Hello,

Here is a pbix file that I created which scrubbed the sensitive information: https://pbmares.sharefile.com/d-sb68872bfc427447084f039c4ea66e28e 

[Ashish_Mathur]

Hi,

Is this the result you want?  Download the PBI file from here.

Hope this helps.

[tortusstorylist]

Hello,

Thank you for looking at this. Unfotunately this does not acheive what I was looking for. What I am trying to do is to keep the overall average by Title in the second column in the first table. Here is a screenshot that might clarify: 

Any additional help would be great.

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = CALCULATE([HoursAverage],ALL(Sheet1[Staff Name]))

Hope this helps.

[tortusstorylist]

That works, thanks! 

[Ashish_Mathur]

You are welcome.