Measure that sums based on 3 columns

[rafsta]

Hi everyone,

I'm trying to create a measure that sums my orders quantity based on the item (string) , orderID (string)  and a date (datetime) at the same time. This columns are in the same table.

In Excel I've used a sumifs that contains all my parameters (with &) and it worked, but now I'm trying to do the same in PBI and I simply can't. Because it gets all values based only in one of these parameters (the item).

I already tried:

Measure=

Var PO= MAX('PO''s'[PO])

Var itens= MAX('PO''s'[Item])

Var data= MAX('PO''s'[Data])

VAR Planejado = CALCULATE(SUM('PO''s'[Planejado]),

FILTER(

ALL('PO''s'),

'PO''s'[Data] = data

&& 'PO''s'[PO] = PO

&& 'PO''s'[Item] = itens

))

RETURN Planejado

But still showing wrong results.

Example:

Its showing 1824000 for both lines in "Planejado", but should look like these 

Item	PO	Data	Planejado
2014661	21000057	21/04/2021	1392000,00
2014661	21000078	14/04/2021	432000,00

[v-easonf-msft · Accepted solution]

Hi, @rafsta

Please  try formula as below:

Measure2 =
CALCULATE (
    SUM ( 'Table'[Planejado] ),
    ALLEXCEPT ( 'Table', 'Table'[Item], 'Table'[PO], 'Table'[Data] )
)

Measure3 = 
VAR PO =
    MAX ( 'Table'[PO] )
VAR itens =
    MAX ( 'Table'[Item] )
VAR data =
    MAX ( 'Table'[Data] )
VAR Planejado =
        SUMX( 
        FILTER (
            ALL ( 'Table' ),
            'Table'[Data] = data
                && 'Table'[PO] = PO
                && 'Table'[Item] = itens
        ),
        'Table'[Planejado]
    )
return 
Planejado

If it doesn't meet your requirement, please share you sample pbix file/data  and details for testing.

Best Regards,
Community Support Team _ Eason

[Anonymous]

You should be using SUMX rather than SUM for these. 

[HashamNiaz]

Hi !

Please share the sample data.

Regards, 

[rafsta]

Ok!