Need Help with grouping and summing

[oluong]

My first table has all the list of possible table name category:

My second table name DataCollection  is a table that collected data based on those category above.  The category are seperated into individual column in the DataCollection table and we mark it 1 if it belongs to it.

How do I make a pivot to sum the above stuff into this:

DESCRIPTION	Counts
I-Broad impact case	4
II-Procedural Appeals	3
III-Disc. findings/Remedies issues/Petitions for Enforcement	1
IV-Inadequate records	12
V-Other Merits appeals	2
VI-Settlement	0
VII-Administrative Closure	5

I try it with the pivot and table visual with no luck.  Thank you so much if anyone can help.

[v-alq-msft · Accepted solution]

Hi, @oluong 

Based on your description, I'd like to suggest you use 'unpivot' and 'group by' feature in 'Query Editor'. I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may make all column selected, click 'Transform' ribbon=>'Unpivot columns'.

Then you need to click 'Group by' in 'Transform' ribbon and set as below. Finally you can rename the columns to get the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[oluong]

Hi Allan,

Thank you so much for your the pbix, I did left out a detail, the reply to amitchandak contains the details.  Hopefully after you read the reply you can give me some more pointers.

[amitchandak]

@oluong , Not very to me transpose or unpivot should help

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[oluong]

Hi amitchandak , I'm so new to this..I forgot to mention I created the column here using:

In the links you gave me, it assume that the columns were to be created in the "Transform Data" using the editor, so I could not use the pivot and unpivot article.  If I have to put these columns Cat I...Cat II using th query editor, I didn't recognize the dax codes, what would your further expertise recommend?

[DataInsights]

In Power Query, select all Category columns (e.g., Cat I, Cat II) in the query DataCollection, and select Unpivot Columns. These columns will be pivoted into two columns: Attribute and Value. Rename these as desired. Then, create a measure that counts the value in each Category:

Category Count = COUNT ( DataCollection[Value] )

Add this measure to a matrix with the Category table (DESCRIPTION field) as rows.