Search code examples
powerbidaxpowerquerypowerbi-desktop

Get a total of employees on the clock every 10 minutes of the day

I am having a big problem, I have a table that has counts of employees the clocked in a a specific time rounded off to 15 minute increments each day. Included in the table are the end times. I am trying to create a bar graph which shows the 15 minutes time windows along the X axis and the Y axis should show the total number of people on the clock at that time however all I can get it to show is how many people "clocked in" at each of those spans. I'm not sure what I am doing wrong. I made a separate time table in 15 minute increments and related it to the employee table and still, same result. ANy help would be greatly appreciated!!!

I am using a simple measure to get the sum. SUMEMPCNT=SUM('data'{EMPCNT])

Here is a sample of the data table:

enter image description here

Here is a sample of the current chart. enter image description here

Solution

  • You can use the build in Quick Measure "Runnung total" to get your DAX formula:

    enter image description here

    It will give you this measure:

    Number running total in TimeStamp = 
CALCULATE(
    SUM('Table'[Number]),
    FILTER(
        ALLSELECTED('Table'[TimeStamp]),
        ISONORAFTER('Table'[TimeStamp], MAX('Table'[TimeStamp]), DESC)
    )
)

    that you can visualize in a clustered column chart:

    enter image description here