Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Average Calculation at different granularities

Hi All,

 

I am trying to calculate Average time to first deal and I was able to get the correct results at the Account Owner level but the aggregated average for a region is not working as needed.

 

Below is my Measure for calculating Avg number of days between 'Hire Date' and First 'Opportunity Close Date' for opportunities created after Hire Date (coming from Employee table) only.

 

Avg time to first deal =
CALCULATE (
    AVERAGEX (
        FILTER (
            OPPORTUNITY,
            OPPORTUNITY[CREATE_DATE]
                >= RELATED ( EMPLOYEE[HIRE_DATE] )
        ),
        DATEDIFF (
            RELATED ( EMPLOYEE[HIRE_DATE] ),
            MIN ( OPPORTUNITY[CLOSE_DATE] ),
            DAY
        )
    )
)

 

This measure is working perfectly at the Employee level but not at the Region level. I believe it's evaluating DateDiff between Hire Date and the earliest opportunity close date of multiple employees under a region when the roll up is happening. At the Region level, I expect the Average of all the employees, not sure how to calculate it. Any help would be appreciated.

 

Example:

 

Results from above Measure:

 

 

In this example, the Avg at the region level should 1419+84/2 .

 

Here is my Model:

 

 

Thanks in advance

Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Hi Anonymous 

     

    try this

     

    Avg time to first deal =
AVERAGEX (
    SUMMARIZE (
        FILTER (
            OPPORTUNITY,
            OPPORTUNITY[CREATE_DATE] >= RELATED ( EMPLOYEE[HIRE_DATE] )
        ),
        EMPLOYEE[HIRE_DATE],
        "@MIN_CLOSE_DATE", MIN ( OPPORTUNITY[CLOSE_DATE] )
    ),
    DATEDIFF ( [HIRE_DATE], [@MIN_CLOSE_DATE], DAY )
)

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

6 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi Anonymous ,

     

    try this.

     

    Avg time to first deal = 
    AVERAGEX (
        FILTER (
            OPPORTUNITY,
            OPPORTUNITY[CREATE_DATE]
                >= RELATED ( EMPLOYEE[HIRE_DATE] )
        ),
        DATEDIFF (
            RELATED ( EMPLOYEE[HIRE_DATE] ),
            OPPORTUNITY[CLOSE_DATE],
            DAY
        )
    )

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi mwegener ,

       

      Thanks for the response. The requirement is to calculate average duration between employees Hire Date and their first Opportunity Close Date (with opportunities created after their Hire date only) so I believe removing MIN will not fix the issue. 

       

       

       

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        6 years ago

         Hi Anonymous 

         

        You are already filtering the opportunity table on this date.

                FILTER (
            OPPORTUNITY,
            OPPORTUNITY[CREATE_DATE]
                >= RELATED ( EMPLOYEE[HIRE_DATE] )
        ),

        The AVERAGEX Function iterates over this filtered table and have to use the CLOSE_DATE of the iteration.

                DATEDIFF (
            RELATED ( EMPLOYEE[HIRE_DATE] ),
            OPPORTUNITY[CLOSE_DATE],
            DAY
        )

        Please try it.

         

        Regards,

        Marcus

        Dortmund - Germany
        If I answered your question, please mark my post as solution, this will also help others.
        Please give Kudos for support.