cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
swatts5883
New Member

summarize row counts in a new column

I am new to Power BI and I'm having trouble. I want to add a column to a table that totals the number of rows for each distinct value in another column. Thus, this input data in my table:

 

CityName

BOS

DTW

CLE

CLE

BOS

BOS

 

would give me this result:

 

City   Total

BOS  3

CLE   2

DTW  1

 

I have been trying to figure it out and I'm stumped. Thanks in advance.

 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Create a new column called "Count" and set it equal to 1:

 

Count = 1

 

Create a new MEASURE called "Total" and set it equal to:

 

Total = COUNT([Count])

 

On report canvas, create a table visualization and add CityName and Total to it.

 

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

9 REPLIES 9
viveksasi94
Helper I
Helper I

Hello everyone,

 

I'm new to power BI, Please help me out with this. My expected input and output tables are as given below.

INPUT

NumberColumn2
1a
2a
1b
4a
2b
4b
4a
1b

 

OUTPUT

Numberab
112
211
421

 

Thanks in advance,

Vivek

Anonymous
Not applicable

Just found this to be unanswered;

 

1. create a duplicate column of column2

2. select both the column2 and duplicate column2 and click on pivot table option available.

 

this will change the dataset into required format;

 

1.PNG2.PNG

Dash
New Member

Easiest solution.

1.Select table in visualizations

2. Add CityName into Values

3. Add CityName into Values again. Now in the dropdown, change the summarization option from don't summarize to COUNT.

 

Greg_Deckler
Super User
Super User

Create a new column called "Count" and set it equal to 1:

 

Count = 1

 

Create a new MEASURE called "Total" and set it equal to:

 

Total = COUNT([Count])

 

On report canvas, create a table visualization and add CityName and Total to it.

 

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

You just solved an issue for me.  Thank you!!

Totally Un-Clear !! How we relate it to the Field ?!

Hi ,

 

I am new to Power BI. Need to show the total count of users, count of distinct users , and count of repeated users for the day.

I am able to get Total count and -6, distinct count -3.  But i need to get the count of repeated users -2  ( Suresh and Mike)

ID Frame        Date                              UserName                

1                     7/1/2016 330 PM         Suresh                   

2                     7/1/2016 330 PM         John3

3                     7/1/2016 337 PM         Mike     

4                     7/1/2016 350 PM         Suresh 

5                     7/1/2016 351 PM         Mike     

6                     7/1/2016 358 PM         Mike                 

Anonymous
Not applicable

Hi swatts5883,

 

   to solve your problem step by step, you have to:

 

1) Select the column

14.png

 

2) Adjust the table using the first row like heading

15.png

 

3) Group By the column "CityName" , choose the new column name , and select the operation "Row count"

 

4) And it's done.

17.png

( . . . )

Let me know if it works.

 

Have a good coding

If you do not want to combine your rows you can add a measure

City total = COUNTA(Sheet1[City])

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors