Count and Group Multiple Columns

[Nosh10]

Hello, 

I have not been able to find an answer to this question and hope someone can help me out. Thank you. 

 I have the following data. 

Gender Group 1	Gender Group 2	Gender Group 3
F	F	M
F	F	M
M	F	F
M	F	F
M	M	F

I would like to create a pie chart that shows two categories "M" and "F" across three columns and the total number of males and females for each category. For example Pie chart shows M = 6 and F = 9. Is this possible in PBI? Thank you. 

Thank you. 

[camargos88]

@Nosh10 ,

Check the attached file.

I've unpivoted the table, like:

[BI_Jo · Accepted solution]

Hi,

To achieve this you can unpivot the table in Power Query Editor:

1. Highlight the 3 columns and use the 'unpivot columns' button in the 'transform' menu:

2. Add a new column called something like 'count' and add the value 1 to each row

3. Set the new column format to 'whole number'

4. You can now create a pie chart like this, setting the 'count' column as the value and the male/female column as the legend:

Hope this helps.

Jo

[Nosh10]

It worked. Thank you so much!