topic Benchmark measure to compare lead quality of one sales rep with others who receive the same leads in DAX Commands and Tips

Benchmark measure to compare lead quality of one sales rep with others who receive the same leads

AlenaSK — Fri, 08 Sep 2023 08:15:38 GMT

Hi!
I want to create a measure that will help me to compare one sales rep results with others, based on the lead quality they work with.
The background:

Each sales rep receives different leads, based on 3 parameters: lead value, lead category, lead type.
Throughout the time, the proportion may differ.

So step 1 will determine the lead quality combo per sales rep. I did it with TOPN function for each parameter separately:
Top1 Lead Potential per SaleRep = TOPN(1, all('table1'[Lead Potential]), Calculate(Counta('table'[Lead Potential])), DESC)
It returns string value, f.e. 'Medium' in this case.

Step 2 should be to calculate AVG number of leads per sales rep who have the same TOP1 Lead Potential, TOP1 lead category and TOP1 lead type.

Any ideas how to proceed with step 2?

Re: Benchmark measure to compare lead quality of one sales rep with others who receive the same leads

Anonymous — Mon, 11 Sep 2023 02:30:13 GMT

Hi AlenaSK ,

Please try this measure.

Avg Leads with Same Top1 Combo = CALCULATE( AVERAGEX( 'table1', COUNTA('table1'[Lead]) ), FILTER( ALL('table1'[SalesRep]), 'table1'[Top1 Lead Potential] = MAX('table1'[Top1 Lead Potential]) && 'table1'[Top1 Lead Category] = MAX('table1'[Top1 Lead Category]) && 'table1'[Top1 Lead Type] = MAX('table1'[Top1 Lead Type]) ) )

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: Benchmark measure to compare lead quality of one sales rep with others who receive the same leads

AlenaSK — Wed, 13 Sep 2023 16:00:05 GMT

Hi Anonymous !
Thank you so much for your suggestion! I'll give it a try!