Most frequent values

[ab00sa7i]

Hi, I have a data looks like this:

I would like to find the first, second, and third most frequent errors for every ID

[Irwan · Accepted solution]

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.

[v-stephen-msft · Accepted solution]

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.

[Ashish_Mathur]

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.

[Irwan · Accepted solution]

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.

[ab00sa7i]

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:

 

https://docs.google.com/spreadsheets/d/1H_nGLtnfXEe4qT3uhuNlPgaC0uWkjBRn/edit?usp=drivesdk&ouid=1008...

[engmohd]

I have same problem and thanks for solution 
but I have more things to solve 

The ranking counts certain values, not all values

I want to specify the faults to be counted, not any values

[Irwan]

Hello @engmohd 

You can add your expression requirement inside FILTER in RANKX. Add you specific faults as expression so it will calculate as your requirement.

Otherwise, please share your sample data (remove any confidential information).

Hope this will help you.

Thank you.