Forum Discussion

oluong's avatar
oluong
Helper I
6 years ago
Solved

Need Help with grouping and summing

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 ...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

DataInsights's avatar
DataInsights
Super User
6 years ago

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.