SUMMARIZE not working as expected

[Eckhardt_david]

Hi,

I'm trying to write a measure to calculate the total stored pallets on 1 warehouse location.

In below example, storage location 408874 has a total of 2 pallets stored.

The measure works fine when I only add field storage location to the Table visual.

But when adding additional fields (for example Load Number which is unique for each pallet), the measure is calculated on each line making the total pallets stored = 4, which is not correct.

My assumption was that by adding a SUMMARIZE function on Storage Location the table would show 1 line with value LOC EUEQ = 2 and the other line would be blank. But I geuss I'm doing something wrong here.

Thanks for your suggestions on how to fix this.

without Load Number:

with Load Number:

measure:

LOC EUEQ =
IF (
     ( [M_Unit Quantity - Inventory Details] <> BLANK () ),
    CALCULATE (
        IFERROR (
            SUMX (
                SUMMARIZE (
                    '02-D_OUT-INV_WAREHOUSE_LOCATIONS',
                    '02-D_OUT-INV_WAREHOUSE_LOCATIONS'[Storage Location],
                    "Count",
                        IF (
                            MAX ( '02-D_OUT-INV_WAREHOUSE_LOCATIONS'[USEFLG] ) = 1
                                || [M_Unit Quantity - Inventory Details] > 0,
                            1,
                            0
                        )
                            * IF ( MAX ( '02-D_OUT-INV_WAREHOUSE_LOCATIONS'[Location Length] ) > 125, 2, 1 )
                            * IF (
                                MAX ( '02-D_OUT-INV_WAREHOUSE_LOCATIONS'[Aisle Id] ) = "99"
                                    || MAX ( '02-D_OUT-INV_WAREHOUSE_LOCATIONS'[Aisle Id] ) = "98",
                                4 / 24,
                                4
                                    / ROUNDDOWN (
                                        4 * 80
                                            / MAX ( '02-D_OUT-INV_WAREHOUSE_LOCATIONS'[Location Width] ),
                                        0
                                    )
                            )
                ),
                [Count]
            ),
            0
        ),
        FILTER (
            '02-D_OUT-INV_WAREHOUSE_LOCATIONS',
            '02-D_OUT-INV_WAREHOUSE_LOCATIONS'[INSTANCE_ID] = "BEBORUL"
        )
    )
)

[AlB]

Hi @Eckhardt_david 

I haven't looked in detail but try adding the new column in the visual to the SUMMARIZE as well:

SUMMARIZE (
                    '02-D_OUT-INV_WAREHOUSE_LOCATIONS',
                    '02-D_OUT-INV_WAREHOUSE_LOCATIONS'[Storage Location],

                    '02-D_OUT-INV_WAREHOUSE_LOCATIONS'[Load Number],

                    "Count",  

                     etc....

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers