summarize row counts in a new column

[swatts5883]

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.

[viveksasi94]

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]

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;

[Dash]

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 · Accepted solution]

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.

[zamboni1199]

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

[MAAbdullah47]

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

[suji]

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]

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]

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

City total = COUNTA(Sheet1[City])