Total of positive values only

[Stemar_Aubert]

Hello,

Consider the following table:

PRODUCT	CATEGORY	SALE	PRICE
A	AA	10	100
B	BB	20	10
C	CC	30	100
D	DD	40	100

I am looking for a DAX measure that would yield the following result:

ProfitRegular = SUMX('TABLE'[SALE]-[PRICE])

Profit = IF([ProfitRegular] < 0, 0, [ProfitRegular])

PRODUCT	PROFIT
A	90
B	0
C	70
D	60
TOTAL	220

The issue is, I can only get following results using an array of solutions:

PRODUCT	PROFIT
A	90
B	0
C	70
D	60
TOTAL	210

While I can get DAX to solve correctly within a table, the total being its own context, it yields an amount not matching what the user is seeing, Effectively, capping the negatives values at 0 isn't working for the total.

Now, I am aware of means such as HASONEFILTER or ISCROSSFILTERED in combination with IF/SWITCH(TRUE() statements, however I also need to be able to filter using CATEGORY, meaning once the user selects multiple categories, everything is crossfiltered and thus the total starts being wrong once more.

Any ideas for a measure that effectively calculate the visual total ?

Thanks!

[AntonioM]

I think you need to include the IF within the SUMX. That way the total will not sum the negative values

SUMX ( IF ( 'TABLE'[SALE]-[PRICE] < 0, 0,  'TABLE'[SALE]-[PRICE] )

[Stemar_Aubert]

Unfortunately, it only works at the lowest level of granularity. Going up, results stop making sense.

[AntonioM · Accepted solution]

Ok, I've tested this one and it looks like it works

Profit = SUMX('Table', IF('Table'[Price]-'Table'[Sale] < 0, 0, 'Table'[Price]-'Table'[Sale]))

[flexecutioner]

I was having a similar challenge with a different dataset and simply could not find or work out a viable solution and this totally saved me - thank you so much!

[Stemar_Aubert]

My mistake, it works! Must have missed something when integrating the if within sumx.

Thanks!