Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Column calculation

Hello!

I have a column DriverID with multiple DriverIDs and another DrivingIndex with numbers generated for those IDs. I want to count how many rows have numbers in them for each DriverID. For example DriverID 283 could have 150 rows of data meant for that DriverID and DriverID 284 could have 170 rows of data and so on.

5 REPLIES 5
Anonymous
Not applicable

@Anonymous Assuming the following scenario meets your requirements:

Amount       ID

50001
100002
150003
500001
2000005
3000006
5000003
4000004
 1
50005
600007
 6
 8
 1
100002
85003
900004
1000005
500006
  

 

You can simply take the COUNT of the amount for ID and only rows with values will be counted.

Please note COUNT(Distinct) will calculate rows with Null values too.

Capture.PNGCapture2.PNG

 

Did I answer your question?

Please mark my solution as Accepted!

Anonymous
Not applicable

@Anonymous 

 

How can i get the "Count of amount" to a single data column like this?

Capture.PNG

 

COUNTX(CombinedTable; CombinedTable[drivingindex])
(I am using this syntax but it counts the total amount of rows with data in them and it doesn't take different IDs into count)
 
This is what it looks like so far:

DriverID

DrivingIndex (Count of rows with values in them and it just ignores the different IDs)
1182432
2182432
3182432
4182432
5182432

 

I want it to look like this:

 

DriverID

DrivingIndex(Count of rows with values in them meant for that ID only)
15
23
3150
420
Anonymous
Not applicable

@Anonymous the 182432 is beacuse of the summarization settings. Please change that to "Don't Summarize" for that calculated column.

I would suggest using a Measure instead. But if you want the column, group by the ID with the Count/CountDistinct of Amount(Referring to my sample data here).

Measure = COUNT(Table1[Amount])
or
 
Capture.PNG
 
Capture2.PNG
 
Did I answer your question?
If yea, please mark my solution Accepted!

@Anonymous 

 

Replace tis formula:

COUNTX(CombinedTable; CombinedTable[drivingindex])

 

with this one

Measure = COUNT(CombinedTable[drivingindex])

OR

Measure = DISTINCTCOUNT(CombinedTable[drivingindex])

 depending on our needs

Anonymous
Not applicable

@themistoklis

 

It still gives me the same number "182432" which is the total number of rows with values in them. I want it to calculate the number of rows with values in them for each ID in to a single column if that's even possible.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.