Forum Discussion

steigelbill's avatar
steigelbill
Frequent Visitor
9 years ago

Multi Level Count and Filter within single day

So, I am trying to use PowerBI analysis to figure out employee throughput. The data set has date & Time, User, Order # and zone.  So I need to count the number of orders done in a zone and divide it by the duration they were in that zone.

 

I can do that!  My problem is calculating when a person changes between zones.

 

Currently to determine duration when someone is in a zone all day, I subtract the start time from end time (and multiply by 24): 

INT Start Time = CALCULATE(MIN(ActivityTracking[Time]),Filter(All(ActivityTracking),ActivityTracking[Zone]="INT"),Filter(All(ActivityTracking),ActivityTracking[User]=Users[User]))

INT End Time = CALCULATE(MAX(ActivityTracking[Time]),Filter(All(ActivityTracking),ActivityTracking[Zone]="INT"),Filter(All(ActivityTracking),ActivityTracking[User]=Users[User]))

 

What I would need to do is sum up the duration of multiple instances of being in a zone, and have that total be the divisor.

 

Any ideas would be appreciated.

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi steigelbill

     

    We can add a row to evaluate the duration per user per zone. Then we can sum up the durations of many instances.

     

    DuringPerUserPerZone =
 (
    CALCULATE (
        MAX ( ActivityTracking[Date Created] ),
        FILTER (
            ActivityTracking,
            ActivityTracking[User] = EARLIER ( ActivityTracking[User] )
                && ActivityTracking[Zone] = EARLIER ( ActivityTracking[Zone] )
                && ActivityTracking[Date Created].[Date]
                    = EARLIER ( ActivityTracking[Date Created].[Date] )
        )
    )
        - CALCULATE (
            MIN ( ActivityTracking[Date Created] ),
            FILTER (
                ActivityTracking,
                ActivityTracking[User] = EARLIER ( ActivityTracking[User] )
                    && ActivityTracking[Zone] = EARLIER ( ActivityTracking[Zone] )
                    && ActivityTracking[Date Created].[Date]
                        = EARLIER ( ActivityTracking[Date Created].[Date] )
            )
        )
)
    * 24

     

    DuringPerUserPerDay =
CALCULATE (
    SUM ( ActivityTracking[DuringPerUserPerZone] ),
    FILTER (
        ActivityTracking,
        ActivityTracking[User] = EARLIER ( ActivityTracking[User] )
            && ActivityTracking[Date Created].[Date]
                = EARLIER ( ActivityTracking[Date Created].[Date] )
    )
)

     

    NumberOfOrders =
CALCULATE (
    COUNT ( ActivityTracking[LPN] ),
    FILTER (
        ActivityTracking,
        ActivityTracking[User] = EARLIER ( ActivityTracking[User] )
            && ActivityTracking[Date Created].[Date]
                = EARLIER ( ActivityTracking[Date Created].[Date] )
    )
)
    Result =
[DuringPerUserPerDay] / [NumberOfOrders]

    Or we can use a measure with the new column.

    MesureResult =
SUM ( ActivityTracking[DuringPerUserPerZone] ) / COUNT ( ActivityTracking[LPN] )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      Hi steigelbill,

       

      Could you please mark the proper post as answer if it worked? More about this topic, please feel free to post here.

      Best Regards!
      Dale