Counting number of potential options across multiple fields

[Anonymous]

We have recently started using Microsoft Lists to complete compliance assessments of elements of our workplace. We've then produced a really useful dashboard to analyse all the data and it's proving really useful.

We have about 20 metrics we are assessing, and each of these elements can have one of the below responses:

1. Correct
2. Not Correct
3. Not Read
4. Input Error
5. N/A

The one thing we're struggling with though is getting all the information into a monthly report we need to send off. We want to produce a matrix or similar that would give us something along the lines of:

Metric name	Correct	Not Correct	Not Read	Input Error	N/A
Metric 1	80	0	5	4	20
Metric 2	78	1	2	4	12

etc etc

Each cell should be a count of how many incidents were recorded with the option in the column header. 

I can't post the exact data, however it comes through from lists along these lines:

Incident number	Metric 1	Metric 2	Metric 3	Metric 4
1	Correct	Not Correct	Correct	Correct
2	Not Correct	Input Error	N/A	Not Correct
3	Correct	Correct	Correct	Correct
4	Input Error	Not Correct	Correct	Correct

etc etc 

Personally I am more used to Excel formulas and know how I would do it there, but in Power BI I keep getting strange layouts that cross reference the information, or nest each metric inside another metric instead of just displaying it all line by line.

I feel like I'm missing something painfully obvious, but I just can't see what it is.

Any support would be much appreciated. First time posting on here, so if i've missed something blaringly obvious out please let me know. 

[parry2k · Accepted solution]

@Anonymous you need to unpivot your data, in transform data, select incident number column -> right click -> unpivot other columns and you will get two columns, Attribute and Value, rename it as you see fit. to visualize the data, use matrix visual, put attribute on rows, value on columns and count on values and you will get following result.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

That's amazing!

I've had to load the data in again as a separate table as otherwise i would lose all the existing work we have (as I don't fancy going through every visual to update to the new standard), but this has worked PERFECTLY.

Thank you so much!