topic Re: Count # of times the name was part of the Top N List in DAX Commands and Tips

Count # of times the name was part of the Top N List

DCComia13 — Tue, 09 Apr 2024 18:40:10 GMT

Good day everyone, I am quite new to power bi and I am stuck on one of the report requirements. I need help in counting the instances an employee becomes part of the Top 10 list per week. I've been searching everywhere on the steps how to do it but I can't seem to find the right solution...

on the sample, I was able to to filter out the top 10 employee (based on the calls with 50-70% Silence time), however, on the #of times in the outlier list column, it keeps on counting the name even if they aren't part of the top 10, thus, it is giving me incorrect result.

on the example: Employee Racoma should only have 1 instance since she is no longer part of the top 10 employee for WE 4/6, however, it's still showing 2 since the formula still counts her name where she's rank 11 on the said week. I hope I explain the scenario well but please let me know if you need more details. Thank you very much!

Re: Count # of times the name was part of the Top N List

tamerj1 — Tue, 09 Apr 2024 19:11:50 GMT

Hi DCComia13

I don't think I got the full picture but the solution could be something like

Count =
CALCULATE ( COUNTROWS ( 'Table' ), ALLSELECTED (), VALUES ( 'Table'[WE_Date] ) )

Re: Count # of times the name was part of the Top N List

DCComia13 — Tue, 09 Apr 2024 19:47:01 GMT

Thank you, tamerj1 , apologies if my explanation is not clear, however, I don't really need the count of rows, what I need is the count of instances the name of the employee showed up on the list. The report I have there is already showing the top 10 outlier agents with the highest % of calls with 50 to 75% silence time. If an agent is part of the list for 2 weeks, then the # of times in the oulier list column should show "2", however, if it's only for one week, the result should be "1".

this is the formula I use, however, it also includes the instances where the agent wasn't part of the top 10 list.

# of Times in the Outlier List =

CALCULATE(DISTINCTCOUNT('table'[WE Date]), ALLSELECTED ('table'), VALUES('table'[Employee]))

Re: Count # of times the name was part of the Top N List

tamerj1 — Tue, 09 Apr 2024 20:00:21 GMT

DCComia13

Please try

Count =
COUNTROWS (
GROUPBY (
FILTER (
CALCULATETABLE (
TOPN (
1,
SUMMARIZE ( 'Table', 'Table'[WE_Date], 'Table'[Emplyee] ),
[Rank Emplyee by Calls], ASC
),
ALLSELECTED ()
),
'Table'[Emplyee] = MAX ( 'Table'[Emplyee] )
),
'Table'[WE_Date]
)
)

Re: Count # of times the name was part of the Top N List

DCComia13 — Tue, 09 Apr 2024 22:33:21 GMT

thanks again tamerj1 , however, this is still not working for me.. 😅

Re: Count # of times the name was part of the Top N List

Anonymous — Wed, 10 Apr 2024 02:57:05 GMT

Hi DCComia13 ,

I made simple samples to calculate the top three name counts for different categories and you can check the results below:

Count = CALCULATE(COUNTAX(FILTER('Table',[Rank]<=3),[We_Date]),ALLEXCEPT('Table','Table'[Employee]))

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

Re: Count # of times the name was part of the Top N List

talespin — Wed, 10 Apr 2024 04:57:03 GMT

hi DCComia13

Can you please share sample pbix with mock data that covers your case.