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

[DCComia13]

 

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!

[talespin]

hi @DCComia13 

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

[v-tianyich-msft · Accepted solution]

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.

[tamerj1]

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] ) )

[DCComia13]

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]))

[tamerj1]

@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]
)
)

[DCComia13]

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