Count Across Multiple Columns

[Phil-osophy]

Hi everyone,

I'm new to Power BI and having some difficulty doing some analysis:

I want to be able to create a filter of the managers name, that when i select a manager, their top three error codes will be returned.

If there is more than one error listed in "Transaction error code" , the column "Error code for analysis" is multiple, and the errors are split up in the columns "Error code for multiple 1...N"

their are 7 error codes in total:

K1  - incorrect invoice amount entered

K2 - incorrect customer name entered

K3 - incorrect dates entered

L1 - customer account no longer exists

L2 - customer didnt submit all documents

M1 - manager approval pending

M2 - other

Here is my dataset:

Here is our expected output :

Thank You!

[Ashish_Mathur]

Hi,

Would you be OK with a 3 column layout of your final output - Name, Type of error and error count?

[Phil-osophy]

Hey @Ashish_Mathur !

that could work, but would that mean that the 2nd and 3rd most frequent errors would be on their own rows? ideally all three would be listed in the same row to be more visually appealing

thanks

[Ashish_Mathur]

Hi,

Yes, they would be on their own rows.  Would you be OK with that?

[Phil-osophy]

Hey @Ashish_Mathur ,

that works just fine!

Thanks

[Ashish_Mathur]

Hi,

Share the link from where i can download your Excel file.

[Phil-osophy]

here it is

https://drive.google.com/file/d/1jVj-ictozIvH8xLmA-iX_nqa4k_TY84P/view?usp=sharing

[Ashish_Mathur]

Hi,

I cannot interpret row 2.  There are 2 types of errors - K2 and M2 but the count is 3.  So does that mean that there are 3 errors each for K2 and M2 or is it split as 2 and 1?  So is the ase with rows 9 and 10.

[Phil-osophy]

Hey!

thats my mistake when entering the values. Row 2 should say 2 errors and row 9 should say 1 error.

Thanks for pointing that out!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Phil-osophy]

Thank you @Ashish_Mathur 

thats a perfect solution!

[Ashish_Mathur]

You are welcome.