Average Calculation at different granularities

[Anonymous]

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

[mwegener]

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]

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]

 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.

[Anonymous]

Hi @mwegener ,

I tried 😊. If you look at the below example there are multiple Close Dates associated with opportunities created after Hire date for an employee but I am only looking for the earliest Opp Close Date so the desired result should be 84.00 not 781.25. I hope I am clear.

Without MIN

With MIN: desired result

[mwegener · Accepted solution]

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.

[Anonymous]

It worked like a charm. Thanks a lot for your time on this.