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

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.

Need Help

Greg_Deckler
10 years ago
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.

9 Replies

Greg_Deckler
Community Champion
10 years ago
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.
- suji
  Advocate I
  9 years ago
  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
- MAAbdullah47
  Helper V
  9 years ago
  Totally Un-Clear !! How we relate it to the Field ?!
- zamboni1199
  Helper II
  8 years ago
  You just solved an issue for me. Thank you!!
Anonymous
Not applicable
10 years ago
Hi swatts5883,

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

1) Select the column

2) Adjust the table using the first row like heading

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

4) And it's done.
( . . . )
Let me know if it works.

Have a good coding
- kazlik
  Helper II
  10 years ago
  If you do not want to combine your rows you can add a measure
  City total = COUNTA(Sheet1[City])
Dash
New Member
9 years ago
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.
viveksasi94
Helper I
8 years ago
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
Number Column2
1 a
2 a
1 b
4 a
2 b
4 b
4 a
1 b

OUTPUT
Number a b
1 1 2
2 1 1
4 2 1

Thanks in advance,
Vivek
- Anonymous
  Not applicable
  6 years ago
  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;