Return name only for max and min or top N average value

DAX DAX Calculations
1

I’m trying to create a measure that returns only employee name from the Activity table. I am looking for the top employee and bottom employee based on average activities completed per day.

The Dax below does not return the correct answer for top.

Top Average Daily Requests =
CALCULATE(
FIRSTNONBLANK(‘Activity’[Employee Name], 1),
FILTER(
‘Activity’,[Daily Requests Rank1]=1
)
)
I’m looking for Jonathan as the top

Capture
Capture886×151 29.1 KB

Here is a sample pbix
Sample.pbix (402.2 KB)

2

I also tried using a summarize table, but could not get that to work properly.

3

Hello @DeanJ,

Thank You for posting your query onto the Forum.

Is this the type of result you’re looking for? Below is the screenshot provided for the reference -

Results
Results1000×313 19.6 KB

To achieve this type of results below are the formulas provided for the reference -

Top Average Daily Requests - Harsh = 
CALCULATE(
    SELECTEDVALUE( Activity[Employee Name] ) , 
        TOPN( 1, 
              ALL( Activity[Employee Name] ) ,
              [Daily Requests Rank1] , 
              ASC ) )

Bottom Average Daily Requests - Harsh = 
CALCULATE(
    SELECTEDVALUE( Activity[Employee Name] ) , 
        TOPN( 1, 
              ALL( Activity[Employee Name] ) ,
              [Daily Requests Rank1] , 
              DESC ) )

I’m also attaching the working of the PBIX file for the reference. And also providing a link of a video based on this topic.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Sample (5) - Harsh.pbix (400.0 KB)

2 Likes
4

@Harsh, Yes! This is exactly what I was looking for. Thank you!

5

Hello @DeanJ,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to help you.

Thanks and Warm Regards,
Harsh