Top 4 Average

[Coryanthony]

Hello All,

I want to find the average audits per hour for the top 4 Auditors. I have my average Audits per hour. Please help

Line Items Actioned = COUNT('All Actioned'[Report Legacy Key])

Line Items Audited = CALCULATE([Line Items Actioned], 'All Actioned'[Selected for Audit] = 1)

Total Hours = SUM('Time Utilization'[Hours])

Hours Audit = CALCULATE([Total Hours], 'Time Utilization'[Stage] = "F&A-TE-Audit")

Hourly Audit = DIVIDE([Line Items Audited], [Hours Audit] ) 

All Actioned and Time Utilization table does not have a direct relationship. Both tables has a relationship with Calendar and Auditor Names Table.

Thank you for your time.

[amitchandak · Accepted solution]

@Coryanthony , Try meausre like

Calculate([Hourly Audit], keepfilter(TOPN, 4, ALL('All Actioned'[Auditor]),[Hourly Audit], desc))

I do not think you need another avg as divide is avg only or try

Calculate(averaged(values('All Actioned'[Auditor]) , [Hourly Audit]) , keepfilter(TOPN, 4, ALL('All Actioned'[Auditor]),[Hourly Audit], desc))

Power BI: rankx, topn, dynamic topn with numeric parameters
https://youtu.be/cN8AO3_vmlY?t=25620

[Coryanthony]

@amitchandak 

Your youtube video really helped me with this one. Thank you,

= CALCULATE([Hourly Audit], TOPN(4,ALLSELECTED('Auditor Names'[Auditor]),[Hourly Audit],DESC), VALUES('Auditor Names'[Auditor])).

[Coryanthony]

@amitchandak 

This one seem to work but appears to be inaccurate.

Top 4 Auditors = Calculate(AVERAGEX(VALUES('All Actioned'[Auditor]),[Hourly Audit]), KEEPFILTERS(TOPN(4,ALL('All Actioned'[Auditor]),[Hourly Audit],DESC)))

[Ashish_Mathur]

Hi,

Does this measure work?

=DIVIDE(SUMX(TOPN(4,VALUES('All Actioned'[Auditor]),[Hourly Audit],DESC),[Hourly Audit]),4)

[Coryanthony]

Hi @Ashish_Mathur Thank you for your response.

It does not appear to accurate.
Top 4 Auditors sample = DIVIDE(SUMX(TOPN(4,VALUES('All Actioned'[Auditor]),[Hourly Audit],DESC),[Hourly Audit]),4)

Top 4 Auditors = Calculate(AVERAGEX(VALUES('All Actioned'[Auditor]),[Hourly Audit]), KEEPFILTERS(TOPN(4,ALL('All Actioned'[Auditor]),[Hourly Audit],DESC)))

 I'd like to see the Average for the top 4 auditors with the highest [Hourly Audit]. For October, the top auditors were Operator 23, 21,38 and 29. I'd like to see their average. 

If i can get their average, this would be the team hourly goal. Thank you

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Coryanthony]

Thank you @Ashish_Mathur 

i was able to get the right measure - 

= CALCULATE([Hourly Audit], TOPN(4,ALLSELECTED('Auditor Names'[Auditor]),[Hourly Audit],DESC), VALUES('Auditor Names'[Auditor]))

[Coryanthony]

Hey @amitchandak 

Thank you for your response. It appears i am getting an error.

First -  Calculate([Hourly Audit], keepfilters(TOPN, 4, ALL('All Actioned'[Auditor]),[Hourly Audit], desc))
Results: The syntax for ',' is incorrect. (DAX(Calculate([Hourly Audit], KEEPFILTERS(TOPN, 4, ALL('All Actioned'[Auditor]),[Hourly Audit], desc)))).

Second - 

Top 4 Auditors = Calculate(AVERAGE(values('All Actioned'[Auditor]) , [Hourly Audit]) , KEEPFILTERS(TOPN, 4, ALL('All Actioned'[Auditor]),[Hourly Audit], desc))

Results: The syntax for ',' is incorrect. (DAX(Calculate(AVERAGE(values('All Actioned'[Auditor]) , [Hourly Audit]) , KEEPFILTERS(TOPN, 4, ALL('All Actioned'[Auditor]),[Hourly Audit], desc)))).