Count occurrences into new table: Custom Column DAX, and Joins

[cmullins_]

I am building a report of rating data. My main table has columns with different metrics and rows with Excellent, Good, Fair or Poor. I want to count the total of each rating type for each metric in a separate table.

Column 1          Column 2              Column 3

Excellent           Good                     Fair

Excellent           Excellent               Poor

Fair                   Good                     Excellent

Poor                  Fair                       Fair

Excellent           Good                    Good

Good                Poor                      Good

New table:

All Columns                Excellent Count              Good Count

Column 1                    3                                    1

Column 2                    1                                    3

Column 3                    1                                    2

I believe I'll need to add a Custom Column in the power query editor, but I am unsure what the DAX should be. Also, I am not sure how to join this new table to the main Fact table.

[v-easonf-msft · Accepted solution]

Hi, @cmullins_ 

It's difficult to create a relationship between new table and fact table based on current fields.

Please try following step to transform data in PowerQuery.

1.unpivot these columns 

2.Group table by New column 'Attribute' and 'Value'

3. pivot column like below:

Result:

Best Regards,
Community Support Team _ Eason