Counting number of entries per second

[Anonymous]

Hi

I need to group and count the number of values that occur within 1 second. I have a date_time down to nano seconds but its only showing the shortest time period of day. I also dont know how to group the data and aggregate the number of values within 1 second. Can someone help?

The data looks something like (i wont write in nanoseconds to make it easier to read)

date_time       value

0                    1.1

0.2                 3.4

0.4                 1.5 

0.6                 2.6 

0.8                 2.1

1.0                 8.3

So the result from this should be one data point 

second    hits/second

1              6

[amitchandak]

Create a new column

new Col = TRUNC(date_time,0)  +1

You can use this column for grouping.  and count the occurrences.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi @amitchandak 

Thanks for the reply, I am new to powerbi so a bit confused. I have created the new column and put the new column as the x-axis in a scatter plot. I then chose my other values as the y and Counted. However it just shows a single point. How can I calculate the occurances per second? The new column with truncated values just gives a single number

[amitchandak]

Refer

https://www.dropbox.com/s/17k61u0nmml5arl/time_mill_sec.pbix?dl=0

You need to add code handle 0.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi 

Unfortunately I cant access the link (it is blocked at my office)

[amitchandak · Accepted solution]

Refer

New Column = if(TRUNC(time_mill_sec[date_time],0)=time_mill_sec[date_time],time_mill_sec[date_time],TRUNC(time_mill_sec[date_time],0)+1)