Forum Discussion
Top 4 Average
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.
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
8 Replies
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=25620Hey 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)))).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)))Hi,
Does this measure work?
=DIVIDE(SUMX(TOPN(4,VALUES('All Actioned'[Auditor]),[Hourly Audit],DESC),[Hourly Audit]),4)
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])).
