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. ...
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.

mwegener's avatar
mwegener
Most Valuable Professional
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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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