Displaying values from multiple columns that can be used in a pie graph

[Anonymous]

Beginner here. Looking for some help.

I have 4 columns that have several values (free text from a survey) but most are metrics (AHT, FCR, etc)

I would like to create a graph that counts and display the most common values between those 4 columns.

Metric 1	Metric 2	Metric 3	Metric 4
Reliability	AHT	eNPS	AHT
AHT	QA SCORE	NPS	N/A
AHT	FCR	eNPS ST	eNPS MT
AHT	FCR	enps ST	enps MT
AHT	FCR	eNPS ST	eNPS MT
Quality	Not applicable	Not applicable	Not applicable
Efficiency	DETRACTOR	QA	Reliability
Detractor	Efficiency Score	Quality Score	N/A
MCRA	Maxsell	Reliability	Not Applicable
Reliability	Productivity	QA	N/A
Productivity	Quality	Reliability	Not Applicable
CPH	Reliability	Work Hours	N/A
Efficiency	Detractors	Quality	Not Applicable
ENPS	AHT	MAE	RELIABILITY
ENPS	FCR	AHT	Not Applicable
MACR	Max Sell	Reliability	QA

AHT - 8

Not applicable - 7

FCR - n

ENPS - n

and so forth.

Then from this measure, I can use to create a pie graph that shows the distribution of the most frequently used to the least.

I appreciate the help!

[johnbasha33 · Accepted solution]

@Anonymous 

To achieve this in Power BI, you can follow these steps:

1. **Combine the Columns**: First, you need to combine the values from all four columns into a single column. You can do this using Power Query Editor.

2. **Count the Occurrences**: Once you have all the values in a single column, you can count the occurrences of each value.

3. **Create a Pie Chart**: Finally, you can use the count of occurrences to create a pie chart to display the distribution of the most common values.

Here's a step-by-step guide:

1. **Combine Columns**:
Open your dataset in Power BI and go to the "Transform Data" (Power Query Editor) window.
- Select the four columns containing your metrics (Metric 1, Metric 2, Metric 3, Metric 4).
- Click on the "Transform" tab and select "Unpivot Columns" to combine the columns into a single column.
- Rename the new column to something like "Metrics".

2. **Count Occurrences**:
- Close the Power Query Editor and return to your report view.
- In the report view, go to the "Modeling" tab and select "New Table".
- Enter the following DAX expression to count the occurrences of each metric:
```
MetricCounts = SUMMARIZE('YourTableName', 'YourTableName'[Metrics], "Count", COUNTROWS('YourTableName'))
```
Replace 'YourTableName' with the name of your dataset.

3. **Create a Pie Chart**:
- With the new table created in the previous step selected, go to the "Visualizations" pane.
- Choose "Pie Chart" visualization.
- Drag the "Metrics" column to the "Legend" field and the "Count" column to the "Values" field.
- You should now have a pie chart showing the distribution of the most common metrics.

This approach will give you a pie chart displaying the distribution of the most frequently occurring metrics. You can then use this visualization to see which metrics are most commonly mentioned in your survey data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!