How to calculate Team's average performance with a distinct count if days?

[Griggs]

Good morning all,

I am hoping someone can shed some light on this matter. I am currently working on a dashboard to show individual's performance compared to the team average. For added context, 'Analyst Average Performance per Day', 'Team Average per Day', 'Team Average' and 'Distinct Count of Days' are all measures. As you can see in the graph below, the 'Team Average per Day' is currently doing Team Average divided by the Distinct Count of Days (839/16), resulting in the 52.4 shown in the graph. However, this is not accurate as not all analysts were working for 16 days, producing a team average that is lower than expected. What I think I need to do (correct me if I am wrong) is total each analyst's performance, then divide that by the amount of people in said team. In the example below, that would be (109.2 + 71.9 + 35.1 + 56.8)/4 = 68.25 (which I believe is a much more accurate representation of the average). I would assume it would look something like the following:

Team Avg per Day = 

CALCULATE(*Function to total the following*(DIVIDE(SUM('KPI Data Dump'[Analyst Performance]),(DISTINCTCOUNT('KPI Data Dump'[Date]))/DISTINCTCOUNT('KPI Data Dump'[Full Name])

The question is, how do I achieve this in Power BI? Any help would be greatly appreciated. 

[v-yalanwu-msft · Accepted solution]

Hi, @Griggs ;

You could create a mesure such as:

Measure = AVERAGEX(  SUMMARIZE(FILTER(ALL('Table'),[KPI]=MAX('Table'[KPI])),[Index], [KPI],"1",SUM([Analyst])/SUM([distinct days])),[1])

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Griggs]

I managed to solve this slightly different to your method with the following: 

CALCULATE(DIVIDE(CALCULATE(SUMX(Values('KPI Data Dump'[Full Name]),[Analyst Average Performance per Day]),REMOVEFILTERS('KPI Data Dump'[Full Name])),DISTINCTCOUNT('KPI Data Dump'[Full Name])),REMOVEFILTERS('KPI Data Dump'[Full Name]))

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523