topic Output calculated table of top 3 employees for each metric in DAX Commands and Tips

Output calculated table of top 3 employees for each metric

rjennings — Thu, 17 Jun 2021 17:11:12 GMT

Hi everyone, so this is a really tricky problem that I've been stuck on all week... I have a dataset of orders and the employees who processed them where we calculate KPIs. I would like to make a report of "Top 3 Employees by Metric" where I have bar charts with the number 1, 2, and 3 employees for a given metric (think olympic podium style) on the x-axis, and their values as 1 (for the number 1 employee), 2 (for number 2), and 3 (for number 3). I'll just stick with three metrics for simplicity's sake, here is the starting data table:

Order Number	Employee	Item Count	Cancelled Count	Return Count
1	Joe	3	2	1
2	Bob	5	0	2
3	Sue	2	1	1

And here is the output table I would like to achieve:

Rank	Item Count	Cancelled Count	Return Count
1	Bob	Bob	Joe
2	Joe	Sue	Sue
3	Sue	Joe	Bob

As you can tell there are situations where I need to break ties (e.g. in the return count metric, Joe and Sue are tied) and I would like to break the ties using the total Item Count column. I've tried using the RANKX and TOPN functions with no luck, though I'm probably doing something wrong. When I can get them to rank at all, many times they are all rank 1, or sometimes the ranks are clearly incorrect (like saying 4 is greater than 10). In addition this output needs to react to changing slicers on the page (e.g. someone choosing a new store with different employees, or a different date range). Any ideas? I need to get a draft of this out by tomorrow and have kinda exhausted all the other options. Thanks!

Re: Output calculated table of top 3 employees for each metric

Jihwan_Kim — Fri, 18 Jun 2021 04:18:12 GMT

Hi, rjennings

Please check the below picture and the sample pbix file's link down below.

Item Count Rank Measure =

VAR selectrank =

SELECTEDVALUE ( Ranking[Rank] )

VAR newtable =

FILTER (

Data,

CALCULATE ( SUM ( Data[Item Count] ) )

= MINX (

TOPN ( selectrank, Data, CALCULATE ( SUM ( Data[Item Count] ) ), DESC ),

CALCULATE ( SUM ( Data[Item Count] ) )

)

RETURN

IF( ISFILTERED(Ranking[Rank]),

MAXX ( newtable, Data[Employee] )

)

Cancelled Count Rank Measure =

VAR selectrank =

SELECTEDVALUE ( Ranking[Rank] )

VAR newtable =

FILTER (

Data,

CALCULATE ( SUM ( Data[Cancelled Count] ) )

= MAXX (

TOPN (

selectrank,

Data,

CALCULATE ( SUM ( Data[Cancelled Count] ) ), ASC

CALCULATE ( SUM ( Data[Cancelled Count] ) )

)

RETURN

IF( ISFILTERED(Ranking[Rank]),

MAXX ( newtable, Data[Employee] )

)

Return Count Rank Measure =

VAR selectrank =

SELECTEDVALUE ( Ranking[Rank] )

VAR newtable =

FILTER (

Data,

100 * CALCULATE ( SUM ( Data[Return Count] ) )

+ 1 / CALCULATE ( SUM ( Data[Item Count] ) )

= MAXX (

TOPN (

selectrank,

Data,

100 * CALCULATE ( SUM ( Data[Return Count] ) )

+ 1 / CALCULATE ( SUM ( Data[Item Count] ) ), ASC

100 * CALCULATE ( SUM ( Data[Return Count] ) )

+ 1 / CALCULATE ( SUM ( Data[Item Count] ) )

)

RETURN

IF( ISFILTERED(Ranking[Rank]),

MAXX ( newtable, Data[Employee] )

)

https://www.dropbox.com/s/ojix223ervisa6k/rjennings.pbix?dl=0