SUM of EARLIER calculation

[emarc1]

Hi,

I have invoice data and I'm trying to get a total number of pallets shipped.

Each order has several lines. I've made a calculated column to show how much of a pallet that takes up ('Parts of pallet').

I've then done an EARLIER calculation to sum up all of the 'Parts of pallet' and then round it up to a full pallet.

Pallets on order = 
CALCULATE(
    ROUNDUP( SUM( Invoices[Parts of pallet] ), 0 ),
    FILTER(
        ALL( Invoices ),
        Invoices[Order No.] = EARLIER( Invoices[Order No.] )
    )
)

What I'm struggling to do is to get it to total this for the order correctly. You should hopefully be able to see what I mean on the image above.

Many thanks.

[v-cherch-msft · Accepted solution]

Hi @emarc1

You may try below measure:

Pallets on order =
VAR a =
    SUMMARIZE (
        Invoices,
        Invoices[Order No],
        "b", ROUNDUP ( SUM ( Invoices[Parts of pallet] ), 0 )
    )
RETURN
    SUMX ( a, [b] )

Regards,

Cherie

[emarc1]

This was helpful thanks. I think also another alternative would be to build it into the data model by having a separate table for order numbers and doing the calculation there.