False result for calculation on lower granularity

[joshua1990]

Hello everybody.

I have a simple calculation of [Inventory]-[Sales]. I need the result if it is negative otherwise, there should be a blank.

In the ItemMaster there are two levels: Area and Item.

My current approach is not working. It shows me no result on Area-level - as you can see:

Area	Item	Sales	Inventory	Inventory-Sales
A1	1	44457,18	12500	-31957,18
	2		215000
	3	486829,7115	315000	-171829,712
Result		531286,8915	542500

The function is:

=IF(SUMX('Item Master';[Inventory])-SUMX(Sales;[Sales])<0;SUMX('Item Master';[Inventory])-SUMX(Sales;[Sales]);BLANK())

Does someone know where my mistake is?

[JustJan]

Hi  @joshua1990 

The filter context on the Result line is the whole table. Totals are not the sum of the indivudal lines, but are calculated according the filter context. 

So if you make sure that your filter context only filters the negative values then the result line will show that value. 

I created two measures under the assumption that the values are in one record (otherwise you need to adjust the measures): 

Inventory_Sales = SUM(ItemMaster[Inventory]) - Sum(ItemMaster[Sales])

Inventory_Sales Negatives Only = SUMX(
filter('ItemMaster', 
'ItemMaster'[Inventory]<ItemMaster[Sales]), 
[Inventory_Sales]
)

Hope this helps.

[az38]

Hi @joshua1990 

try to use calculate for correct row context usage and maybe SUM instead of SUMX

for example

Measure =
var Expression = CALCULATE(SUM('Item Master'[Inventory]))-CALCULATE(SUM(Sales[Sales]))
RETURN
IF (Expression < 0;Expression; BLANK())

do not hesitate to give a kudo to useful posts and mark solutions as solution

[cpearson]

Hi Joshua1990

Can you just clarify what you mean regarding your two levels, I don't understand, do you mean there are two types of record in the data table? Could you include a screen shot of the data?

Are you expecting a value in the Inventory-Sales column against the Result row? It's blank because it's not a negative number.

[joshua1990]

Hey @cpearson Thanks for your support!

I just mean there are two granularities like this:

Area	Item
A	A1
A	A2
B	B1

I would expect the sum of both negative values 🙂