Forum Discussion

Coryanthony's avatar
Coryanthony
Helper III
2 years ago
Solved

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    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

amitchandak's avatar
amitchandak
Super User
2 years ago

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's avatar
    Coryanthony
    Helper III
    2 years ago

    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)))).


     

  • Coryanthony's avatar
    Coryanthony
    Helper III
    2 years ago

    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's avatar
      Ashish_Mathur
      Super User
      2 years ago

      Hi,

      Does this measure work?

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

      • Coryanthony's avatar
        Coryanthony
        Helper III
        2 years ago

        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
         
         
  • Coryanthony's avatar
    Coryanthony
    Helper III
    2 years ago

    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])).