Forum Discussion

SantiagoTorres's avatar
SantiagoTorres
Helper I
3 years ago
Solved

Average

Hi All, hope you're well,

 

Can you please help me with this? I'm trying to get the average site/visits per company, and I got the following measures.

 

Site  Visits = COUNTROWS(SUMMARIZE('3. CoW Productivity',DimProject[Project Number],'Calendar'[Date], DimCoWInsp[CoWInsp]))
 
Workdays = COUNTROWS(SUMMARIZE('3. CoW Productivity','Calendar'[Date]))
 
Site Visits/day =  DIVIDE( [Site  Visits] , [Workdays])
 
CoWCompCoWInspSite  VisitsWorkdaysSite Visits/day
Company 1Inspector 1151403.78
Company 1Inspector 214944293.48
Company 1Inspector 344133.38
Company 1Inspector 460183.33
Company 1Inspector 513604143.29
Company 1Inspector 661193.21
Company 1Inspector 711443693.10
Company 1Inspector 811883843.09
Company 2Inspector 98052722.96
Company 1Inspector 1032112.91
Company 3Inspector 11227802.84
Company 1Inspector 121762.83
Company 2Inspector 137272862.54
Company 2Inspector 14246972.54
Company 3Inspector 154761992.39
Company 3Inspector 1640172.35
Company 2Inspector 176302802.25
Company 3Inspector 1828142.00
Company 2Inspector 191981181.68
Company 2Inspector 201661021.63
Company 3Inspector 21641.50
Company 2Inspector 2247321.47
Company 2Inspector 221571101.43
Company 3Inspector 2347351.34
Company 3Inspector 2456471.19
Company 2Inspector 25441.00
Company 2Inspector 26661.00
  941749319.10

 

I need to get to these average site visits/day values calculated in Excel.

 

Company 13.24
Company 21.85
Company 31.95 
 Avg2.34

 

Thanks as always,

Sanitago Torres

Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    Please try this one instead.

     

    Avg Measure =
AVERAGEX (
    VALUES ( T4[CoWComp] ),
    [Site Visits/day]
)

     

    Pat

11 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    Please try this measure expression. Replace T4 with your actual table name.

    Avg Measure =
AVERAGEX (
    VALUES ( T4[CoWComp] ),
    CALCULATE ( AVERAGEX ( t4, T4[Site  Visits] / T4[Workdays] ) )
)

    Pat

     

    • SantiagoTorres's avatar
      SantiagoTorres
      Helper I
      3 years ago

      Hi Pat, thanks for your response,

      When you get a gap, can you please tell me if Im doing something wrong here,

       

      Avg Site Visits/Day = AVERAGEX( VALUES( DimCoWCom[CoWComp] ),
                                            CALCULATE( AVERAGEX('2. Observations', [Site  Visits] / [Workdays] ) ) )
                                            

      These are the results I'm getting, which are not correct.

       

      CoWCompAvg Site Visits/Day
      Company 11
      Company 21
      Company 31

       

      Thanks for your time,

       

      Santiago Torres

      • SantiagoTorres's avatar
        SantiagoTorres
        Helper I
        3 years ago

        Hi Pat ppm1 , thanks for your response,

        When you get a gap, can you please tell me if Im doing something wrong here,

         

        Avg Site Visits/Day = AVERAGEX( VALUES( DimCoWCom[CoWComp] ),
                                              CALCULATE( AVERAGEX('2. Observations', [Site  Visits] / [Workdays] ) ) )
                                              

        These are the results I'm getting, which are not correct.

         

        CoWCompAvg Site Visits/Day
        Company 11
        Company 21
        Company 31

         

        Thanks for your time,

         

        Santiago Torres

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Share the download link of the PBI file and show the expected result there.

    • SantiagoTorres's avatar
      SantiagoTorres
      Helper I
      3 years ago

      Hi Ashish Ashish_Mathur , hope you're well. Sorry for the late reply,

       

      I'm kind of new to interacting here, and I dont know how to provide the information you're requesting. I couldn't find a way to load a file. Regarding the expected results, please refer to my first post; I believe I provided all the information there.

       

      Thanks for your help,

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

        Upload the file to Google Drive and share the download link here.  Show the expected result clearly.