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

[AlenaSK]

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?

[Anonymous · Accepted solution]

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. 

[AlenaSK · Accepted solution]

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

[Anonymous · Accepted solution]

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.