Column calculation

[Anonymous]

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.

[Anonymous]

@Anonymous Assuming the following scenario meets your requirements:

Amount       ID

5000	1
10000	2
15000	3
50000	1
200000	5
300000	6
500000	3
400000	4
	1
5000	5
60000	7
	6
	8
	1
10000	2
8500	3
90000	4
100000	5
50000	6

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.

Did I answer your question?

Please mark my solution as Accepted!

[Anonymous]

@Anonymous 

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

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)
1	182432
2	182432
3	182432
4	182432
5	182432

I want it to look like this:

DriverID	DrivingIndex(Count of rows with values in them meant for that ID only)
1	5
2	3
3	150
4	20

[Anonymous]

@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

Did I answer your question?

If yea, please mark my solution Accepted!

[themistoklis]

@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]

@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.