Show trend line for Top Performing Contractors

[Anonymous]

Hi,

I have a report in which there is a requirement to display scores for a particular contractor over a period of time. The score value is a measure. There is a slicer on the contractor which drives this report. However, there is an additional requirement now with which users want to see a trend of the average score in the same chart for top 20 contractors with highest average score.

I can achive that in a separate chart all together, but is there any option to have it in same chart. The Top 20 contractors can vary over time. So it is important to rank top 20 contractors every year based on their average score and then display the trend line based on that. Any pointers will be very helpful.

[v-lili6-msft]

hi, @Anonymous 

You may try this way:

Step1:

Add a rank measure for contractors average score.

Step2:

For top 20 contractors average score, add the condiftional by rank maeasure in calculation.

If not your case, please share some simple sample data and expected output.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft 

Thanks for your reply. This probably does not work for me as the Average score is also a measure. I have now attached the desktop file for your reference at this link https://1drv.ms/f/s!Aiuhygz8zTaWZw5iKz3FxbnadPk 

There is a dual axis chart which is driven by a Contractor dropdown slicer. My requirement is to add another line for the average scores of top performing contractors and should not change based on the slicer selection.

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could try this way:

Step1:

Add a rank measure of contractor by vaerage score

Rank = RANKX(ALL(evaluations_of_contractors[Contractor]),[Average Score],,DESC,Skip)

Step2:

Add the top20 contractor average

Top20 Average Score = CALCULATE([Average Score],FILTER(ALL(evaluations_of_contractors[Contractor]),[Rank]<=20))

Result:

and here is pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Hello @v-lili6-msft ,

It worked like a miracle, thanks for the support.