Forum Discussion
Solved
Sum by multiple filters for hour range
Please Help I have a large dataset with sales information aggregated by date, store#, and most imporantly by hour. See example below; Date StoreId Hour Online Orders Phone Orders Total Orde...
Here's a pattern for a calculated column. You can adjust the threshold variable as well as the logic for handling cases where three consecutive hours don't exist.
Flag = VAR vThreshold = 175 VAR vHour = Table1[Hour] VAR vTotalOrders = Table1[Total Orders] VAR vOnlineOrdersLast3Hours = CALCULATE ( SUM ( Table1[Online Orders] ), ALLEXCEPT ( Table1, Table1[Date], Table1[StoreId] ), Table1[Hour] >= vHour - 2, Table1[Hour] <= vHour ) VAR vResult = IF ( vOnlineOrdersLast3Hours == 0 && vTotalOrders > vThreshold, "T", "F" ) RETURN vResult
DataInsights
Super User
Super User
Here's a pattern for a calculated column. You can adjust the threshold variable as well as the logic for handling cases where three consecutive hours don't exist.
Flag =
VAR vThreshold = 175
VAR vHour = Table1[Hour]
VAR vTotalOrders = Table1[Total Orders]
VAR vOnlineOrdersLast3Hours =
CALCULATE (
SUM ( Table1[Online Orders] ),
ALLEXCEPT ( Table1, Table1[Date], Table1[StoreId] ),
Table1[Hour] >= vHour - 2,
Table1[Hour] <= vHour
)
VAR vResult =
IF ( vOnlineOrdersLast3Hours == 0 && vTotalOrders > vThreshold, "T", "F" )
RETURN
vResult
I cannot appreciate this enough!