Forum Discussion

BI_Noob's avatar
BI_Noob
Frequent Visitor
4 years ago

Sum Aggregated Values With Multiple Filters based on Dynamic Range

 
Please Help

I have a large dataset with sales information aggregated by date, store#, and most imporantly by hour. See Below

Date _ StoreId _ Hour _ Online Orders _ Phone Orders _ Total Orders
Jan 1 _ 1X _ 10AM _ 50 _ 100 _ 150

 

Essentially - I'm trying to create a column flag of T/F to identify when a store has 3 consecutive hours of Online sales = 0 but Total Orders were > X. 

=AND(SUMIFS(OnlineOrders,DATE,[@DATE],StoreId,[@StoreId],Hour,OR([@Hour],[@Hour]+1,[@Hour]-1)) = 0, SUMIFS(TotalOrders,DATE,[@DATE],StoreId,[@StoreId],Hour,OR([@Hour],[@Hour]+1,[@Hour]-1)) > X

 

Can someone please help?

Need Help

5 Replies

    • BI_Noob's avatar
      BI_Noob
      Frequent Visitor
      4 years ago

      Hi Amit, 

      I watched the video and I'm not too sure if it's the same. Attached you'll find a sample dataset. Here's how I got the column to calc what I'm trying to accomplish

       

       

      =AND(SUM(SUMIFS([Online Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]),SUMIFS([Online Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]+1),SUMIFS([Online Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]-1)) = 0,
      SUM(SUMIFS([Phone Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]),SUMIFS([Phone Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]+1),SUMIFS([Phone Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]-1)) > 0)

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        BI_Noob , Please provide the same in table format. So that I can try.  Copy paste from excel.

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi BI_Noob ,

     

    The same result export by this DAX expression:

    Column =
VAR _f =
    FILTER (
        'Table',
        [Date] = EARLIER ( 'Table'[Date] )
            && [StoreId] = EARLIER ( 'Table'[StoreId] )
            && [Hour]
                >= EARLIER ( 'Table'[Hour] ) - 1
            && [Hour]
                <= EARLIER ( 'Table'[Hour] ) + 1
    )
RETURN
    AND ( SUMX ( _f, [Online Orders] ) = 0, SUMX ( _f, [Phone Orders] ) > 0 )

     

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.