Forum Discussion
Sum Distinctcount per Timestamp
Hello,
I am trying to make a DAX to sum the distinct counts of Names per timestamp and display the result per hour. See the example below
| assigned | Score | DateOfAnalysis-Date | DateOfAnalysis-Hour |
| A | 10.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| B | 10.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| C | 125.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| D | 100.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| E | 50.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| G | 60.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| H | 70.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| I | 110.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| J | 25.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| K | 10.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| L | 45.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| M | 40.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| N | 10.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| O | 50.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| P | 50.00% | 7/25/2023 0:00 | 1899-12-30 15:04:00 |
| C | 125.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| D | 100.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| G | 110.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| H | 70.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| I | 60.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| J | 25.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| K | 10.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| L | 45.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| M | 40.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| N | 10.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| O | 60.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| P | 60.00% | 7/25/2023 0:00 | 1899-12-30 15:28:54 |
| C | 125.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| D | 100.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| F | 50.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| G | 160.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| H | 70.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| I | 60.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| J | 25.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| K | 10.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| L | 45.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| M | 30.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| N | 50.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| O | 10.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
| P | 60.00% | 7/25/2023 0:00 | 1899-12-30 15:53:37 |
The result I get in PBI with the formula EngineerPerDoA = sumx(values(imtScoreData[DateOfAnalysis-Hour]),DISTINCTCOUNT(imtScoreData[assigned])) I get the correct result per timestamp 15, 12 and 13.
Now when I want to display this per hour, I get 48 as a result instead of 40
How can I make a formula to sum those 15,12,13 values to display the 40 in my hour bin layout ?
thank you
No RepliesBe the first to reply