Multi Level Count and Filter within single day

[steigelbill]

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.

[v-jiascu-msft]

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]

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