Forum Discussion
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. ...
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.
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
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.
It worked like a charm. Thanks a lot for your time on this.