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. Co...
Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    Please try this one instead.

     

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

     

    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

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    Looks like you are referencing measures in your inner AVERAGEX, while I used columns. Please switch to column references and it should work.

     

    Pat

    • SantiagoTorres's avatar
      SantiagoTorres
      Helper I
      3 years ago

      Hi Pat ppm1, that's correct. Can you please elaborate a bit more? As you can see in my first post, these two DAX measures are coming from the data I got from previous years (massive table), so I dont have two custom columns. Hope this makes sense to you.

       
      Site  Visits = COUNTROWS(SUMMARIZE('3. CoW Productivity',DimProject[Project Number],'Calendar'[Date], DimCoWInsp[CoWInsp]))
       
      Workdays = COUNTROWS(SUMMARIZE('3. CoW Productivity','Calendar'[Date]))
       
      Thanks,
       
      • ppm1's avatar
        ppm1
        Solution Sage
        3 years ago

        Please try this one instead.

         

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

         

        Pat