Forum Discussion
Most frequent values
Hi, I have a data looks like this:
I would like to find the first, second, and third most frequent errors for every ID
hello ab00sa7i
please check if this accomodate your need.
the easiest way to achive this is by using SUMMARIZE.
1. create new table with SUMMARIZE including with count of error
Summarize =
SUMMARIZE(
'Table',
'Table'[ComputerID],
'Table'[Error Type],
"Count Error",
COUNT('Table'[Error Type])
)2. create calculated column for ranking or indexing (to show first, second, third most frequent error).
Rank =
RANKX(
FILTER(
'Summarize',
'Summarize'[ComputerID]=EARLIER('Summarize'[ComputerID])
),
'Summarize'[Count Error],
,
DESC
)Result:
Hope this will help you.
Thank you.
Hi ab00sa7i ,
You can try Irwan 's workaround.
Here I want to share another possible workaround.
You can create sort field group by Computer ID in Power Query.
1.Group by
2.Add index
Then you can easily filter the third error in Desktop.
For example:
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
hello ab00sa7i
please check if this accomodate your need.
the easiest way to achive this is by using SUMMARIZE.
1. create new table with SUMMARIZE including with count of error
Summarize =
SUMMARIZE(
'Table',
'Table'[ComputerID],
'Table'[Error Type],
"Count Error",
COUNT('Table'[Error Type])
)2. create calculated column for ranking or indexing (to show first, second, third most frequent error).
Rank =
RANKX(
FILTER(
'Summarize',
'Summarize'[ComputerID]=EARLIER('Summarize'[ComputerID])
),
'Summarize'[Count Error],
,
DESC
)Result:
Hope this will help you.
Thank you.
I have same problem and thanks for solution
but I have more things to solveThe ranking counts certain values, not all values
I want to specify the faults to be counted, not any values
Thanks, this work for me
But if I have another table:
and I need to filter some values (I need to apply rank in some error not all, only High errors like shown in table), I tried to add it in filter function but it doesn't work or I didn't know how, can you please show me how?
For this data:
Hi ab00sa7i ,
You can try Irwan 's workaround.
Here I want to share another possible workaround.
You can create sort field group by Computer ID in Power Query.
1.Group by
2.Add index
Then you can easily filter the third error in Desktop.
For example:
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share data in a format that can be pasted in an MS Excel file. For that data, show the expected result. Also, what result do you expect if there is a tie in the number of errors - show the result clearly.
