Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |