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

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

    • 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,