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