cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
freelensia Regular Visitor
Regular Visitor

Convert each value of column B into column headers, grouped by column A

I have a table Bounced Emails as follow:

 

Email         Bounce_Type
a@gmail.com hard
a@gmail.com soft
b@gmail.com hard
c@gmail.com soft

 As you can see, this table has no unique ID column! E-mails may repeat.

 

I want to sum over the first 2 columns like this.

Email         Hard count   Soft Count
a@gmail.com 1 1
b@gmail.com 1 0
c@gmail.com 0 1

 

I tried group by options but it did not yield good results.

 

Could somebody help me?

 

Thanks!

 

Freelensia

 

1 ACCEPTED SOLUTION

Accepted Solutions
freelensia Regular Visitor
Regular Visitor

Re: Count grouped by column A for each value of column B

I ended up doing this:

 

Let's say you have a table like this:
Site Type
  
You want something equivalent to dragging the Type variable from row to column in Excel's pivot table.
 
How to do it
1. Do a group by Site and Type
You will get a second table like this:
Site Type Count
 
2. Select the Type column, right-click, choose [Pivot Column].
In the next window, choose to Aggregate [Count] column created in previous step. expand [Advanced Options] and choose [Sum]
 
You will get the table you want:

View solution in original post

4 REPLIES 4
bccolema Regular Visitor
Regular Visitor

Re: Count grouped by column A for each value of column B

I think you could do that with 2 measures.   1 for each (hard, soft), the idea would then be to setup an IF statement to populate the field if it is hard or soft.

So a calculation you could do would be 

 

ishard = If(Table1[BounceType] = "hard", 1, null)

issoft = If(Table1[BounceType] = "soft", 1, null)

 last step would just be to count the values 

freelensia Regular Visitor
Regular Visitor

Re: Count grouped by column A for each value of column B

Thanks for the answer!

 

Sorry but I am completely new to PQ. Could you provide a bit more details on each step?

Super User IV
Super User IV

Re: Count grouped by column A for each value of column B

Hi,

 

Create a matrix visual.  Drag Email to the row labels, Boune_type to the column labels and write this measure

 

=COUNTROWS(Data)

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
freelensia Regular Visitor
Regular Visitor

Re: Count grouped by column A for each value of column B

I ended up doing this:

 

Let's say you have a table like this:
Site Type
  
You want something equivalent to dragging the Type variable from row to column in Excel's pivot table.
 
How to do it
1. Do a group by Site and Type
You will get a second table like this:
Site Type Count
 
2. Select the Type column, right-click, choose [Pivot Column].
In the next window, choose to Aggregate [Count] column created in previous step. expand [Advanced Options] and choose [Sum]
 
You will get the table you want:

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors