Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Column Filtering within Summarize

Good day I am working with a table created called OTIF Table to calculate fulfillments in complete deliveries and on time of purchase orders, I have created the OTIF Table with summarize where it bri...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    You can update the formula of calculated table as below, please find the details in the attachment.

    OTIF Table = 
SUMMARIZE (
    FILTER (
        'PurchaseOrderDetail',
        'PurchaseOrderDetail'[DateReceived]
            <= CALCULATE (
                MAX ( 'PurchaseOrder'[EstShipDate] ),
                FILTER (
                    'PurchaseOrder',
                    'PurchaseOrder'[PONumber] = EARLIER ( 'PurchaseOrderDetail'[PONumber] )
                )
            )
    ),
    PurchaseOrderDetail[PONumber],
    "Qty Order", SUM ( PurchaseOrderDetail[QuantityOrdered] ),
    "Qty Received", SUM ( PurchaseOrderDetail[QuantityReceived] ),
    "Date Received", MAX ( PurchaseOrderDetail[DateReceived] ),
    "ETA", MAX ( PurchaseOrder[EstShipDate] )
)

    PurchaseOrderDetail and PurchaseOrder table

    Summarize table OTIF Table

    Best Regards

amitchandak's avatar
amitchandak
Super User
3 years ago

Syndicate_Admin , Try like

 

Change it like

"Qty Received",calculate(sum(PurchaseOrderDetail[QuantityReceived]),filter(PurchaseOrderDetail,PurchaseOrderDetail[DateReceived]<PurchaseOrder[EstShipDate]))

 

 

or after summarize

filter(

SUMMARIZE(PurchaseOrderDetail,PurchaseOrderDetail[PONumber],
"Qty Order",sum(PurchaseOrderDetail[QuantityOrdered]),
"Qty Received",sum(PurchaseOrderDetail[QuantityReceived]),
"Date Received",Max(PurchaseOrderDetail[DateReceived].[ Date]),
"ETA",Max(PurchaseOrder[EstShipDate].[ Date])), [Date Received]<[ETA] )

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    Syndicate Admin

    I do not find the proposal when performing the filter after annotating the table PurchaseOrderDetail does not let me put the condition <= PurchaseOrder EStShipDate

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous ,

      You can update the formula of calculated table as below, please find the details in the attachment.

      OTIF Table = 
SUMMARIZE (
    FILTER (
        'PurchaseOrderDetail',
        'PurchaseOrderDetail'[DateReceived]
            <= CALCULATE (
                MAX ( 'PurchaseOrder'[EstShipDate] ),
                FILTER (
                    'PurchaseOrder',
                    'PurchaseOrder'[PONumber] = EARLIER ( 'PurchaseOrderDetail'[PONumber] )
                )
            )
    ),
    PurchaseOrderDetail[PONumber],
    "Qty Order", SUM ( PurchaseOrderDetail[QuantityOrdered] ),
    "Qty Received", SUM ( PurchaseOrderDetail[QuantityReceived] ),
    "Date Received", MAX ( PurchaseOrderDetail[DateReceived] ),
    "ETA", MAX ( PurchaseOrder[EstShipDate] )
)

      PurchaseOrderDetail and PurchaseOrder table

      Summarize table OTIF Table

      Best Regards