Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
oluong
Helper I
Helper I

Need Help with grouping and summing

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

oluong_0-1593979646000.png

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.

 
 

temp.png

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

DESCRIPTIONCounts
I-Broad impact case4
II-Procedural Appeals3
III-Disc. findings/Remedies issues/Petitions for Enforcement1
IV-Inadequate records12
V-Other Merits appeals2
VI-Settlement0
VII-Administrative Closure5

 

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

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

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:

f1.png

 

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

f2.png

f3.png

 

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

f4.png

f6.png

 

Best Regards

Allan

 

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

View solution in original post

5 REPLIES 5
v-alq-msft
Community Support
Community Support

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:

f1.png

 

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

f2.png

f3.png

 

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

f4.png

f6.png

 

Best Regards

Allan

 

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

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
Super User
Super User

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

 
 
 
 
 

Untitled.png

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
Super User
Super User

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.





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

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.