Measure to calculate based on current row

[cgaralde]

HI,

I am trying to create a measure to show the total quantity based on Location and Type. 

Location	Type	Quantity
Loc1	0	10
Loc2	1	2
Loc1	1	4
Loc1	2	3
Loc2	1	4
Loc1	0	5

I have the following formula:

Rcvd =

CALCULATE(

SUM([Quantity]),

'Item'[Type] = "0",

'Item'[Location] = EARLIER('Item'[Location])

)

but EARLIER does not seem to work on this context.  Filtering on Type is fine as it is a static value, but how do I filter on the same location as the current row.  Any help is appreciated. 🙂

[Jos_Woolley · Accepted solution]

Hi,

But this is a measure, correct, not a calculated column? So you can simply use:

Rcvd =
CALCULATE ( SUM ( [Quantity] ), 'Item'[Type] = 0 )

which, when placed in e.g. a table visual with along with Location will produce:

Location	Rcvd
Loc1	15
Loc2

Regards

[cgaralde]

Huge facepalm moment for me.  Of course that is the answer.  I was breaking my head on this trying to base it off my SQL query.  Thanks.