Top 5 non conformities with text data

[BenCha]

Hey guys,

I'm starting with Power Bi and I'm learning and enjoying it a lot, but I don't know all the possibilities of this tool.

For work purpose I need to do a report about the non conformities in audits.

I'm trying to pull the Top 5 non conformities based on the number of "Non Conforme" in each of the 34 questions numbered from 1. to 34.

One issue is the export from the software (that I can't modify) is exporting some text that the auditors are adding when there is a non conformities in the same cell. For this I managed to create an extra column for each non conformities where I take only the "Non conforme" part. So I managed to clean a bit.

But then I don't know how to be able to sort the non conformities by number of "Non conforme" in the column. I'd use the count.if in excell but not possible in Power BI.

I still need the Site info, date and other info that are removed in the excell file for privacy reasons, to be able to filter.

The first column is a unique number per audit so one line is one audit.

Find below the link to the file, a SC of the file and the kind of report I would like to have.

Tell me if you ahe any questions:

Classeur1.xlsx

[AnkitKukreja]

Hi! @BenCha 

Do you have ranking against this data? If not then add additional column in Power query, index should work or if you have any order that you need, use conditional column. Then in table view use sort option and choose sort by your (number column).

[v-tangjie-msft · Accepted solution]

Hi @BenCha ,

(1) Select the 34 problem columns in Power query and click on Unpivot Columns.->Then add a conditional column.->Click on "close and apply"

(2) We can create measures.

Count_Non = COUNTROWS(FILTER('Table', 'Table'[Custom]="Non Conforme"))

Rank = RANKX(SUMMARIZE(ALLSELECTED('Table'),[N°],"count",[Count_Non]),[Count_Non],[Count_Non],DESC,Dense)

Flag = IF([Rank]<=5,1,0) 

(3) Place [Flag=1] on the visual object.

If I have misunderstood you, please clarify in a follow up reply with an example of your expected output.

Best Regards,

Neeko Tang

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