Creating a Measure with filters on both negative and positive values

[CleverUser23]

Hi,

Hi I'm trying to build a measure that would sum all variations over or equal to 1M and under 10M or over -10M and under or equal to -1M.

In other words:

• the number must be > -10,000,000 && <=-1,000,000
• or, the number must be <10,000,000 && >=1,000,000

I've tried the following but it is not working:

Weekly Delta:=CALCULATE(sum('Table'[DeltaHebdoCAD]))

Weekly1M:=CALCULATE([Weekly Delta], FILTER('Table','Table'[DeltaHebdoCAD]<10000000 && 'Table'[DeltaHebdoCAD]>=1000000), FILTER('Table','Table'[DeltaHebdoCAD]>-10000000 && 'Table'[DeltaHebdoCAD]<=-1000000))

Thank you,

[Anonymous · Accepted solution]

Give this a try.

Weekly1M := CALCULATE(
	[Weekly Delta], 
	ABS('Table'[DeltaHebdoCAD]) < 10000000,
	ABS('Table'[DeltaHebdoCAD]) >= 1000000
)

[Anonymous · Accepted solution]

Give this a try.

Weekly1M := CALCULATE(
	[Weekly Delta], 
	ABS('Table'[DeltaHebdoCAD]) < 10000000,
	ABS('Table'[DeltaHebdoCAD]) >= 1000000
)

[CleverUser23]

Thank you for taking the time to awnser me Ross.

Your measure works, bu there is something I forgot to mention.

What if I needed to retain only net position of every group in my sum?

Let's say I have 3 groups, A B and C with the following numbers associated.

• A should not be retain because he's got a net position of 950k.
• B should be retain because his position is -1200k.
• C should be retain because hes got a net position of 1100k.

My ultimate goal is here is to show net position of groups that have a net position of less than 10M but over 1M (In absolute numbers).

Regards,

[Anonymous · Accepted solution]

I'm not sure what you are asking.  The ABS function i've used is for an Absolute Value.  That means that both negative and positve numbers are counted as positive numbers.

If i understand you correctly, simply need a formula that is 

Net Position = ABS(SUM('Table'[DeltaHebdoCAD]))