CODEWITHSUNDEEP
powerbi
Student of the Digital World
Student of the Digital World

Reputation: 3041

PowerBI - Calculate the number of occurance of a value in a column using calculated field

I am trying to calculate the number of times a value occurs in a column by using calculated field. Measure can do this a bit more easily, but measure limits my usage for creating other columns and the usages in the dashboards.

City

BOS

DTW

CLE

CLE

BOS

BOS

Expected Output:    

City   Total

BOS  3

CLE  2

DTW  1

Using the the option of Groupby in the query editor neglects the other columns which are on my data.

Upvotes: 3

Views: 21837

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

If you want your output as its own table then you can do:

CityCount = SUMMARIZECOLUMNS(Cities[City], "Total", COUNT(Cities[City]))

If you want it as a calculated column:

Total = COUNTROWS(FILTER(Cities, Cities[City] = EARLIER(Cities[City])))

(Assuming your table name is Cities.)

Upvotes: 5

Related Questions