Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 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
How can i get the "Count of amount" to a single data column like this?
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 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).
@Anonymous
Replace tis formula:
COUNTX(CombinedTable; CombinedTable[drivingindex])
with this one
Measure = COUNT(CombinedTable[drivingindex])
OR
Measure = DISTINCTCOUNT(CombinedTable[drivingindex])
depending on our needs
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.
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |