Forum Discussion
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 divi...
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
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