Matrix Total incorrect when collapsed

[jdebett]

Hi All,

i have a table that looks like this:

Location	Material	Order	Order $	Stock	Inv$	Work Orders	WorkOrder $	Total
new York	Apples	-1						-1
new York	Apples	-5						-5
new York	Apples	-25						-25
new York	Apples	-70						-70
new York	Apples			10				10
new York	Apples					25		25
new York	Apples					25		25
new York	Apples					25		25
new York	Apples					25		25
new York	Apples	-60						-60
new York	Apples	-10						-10
new York	Apples					25		25
new York	Apples					25		25
new York	Apples					25		25
new York	Apples					25		25
New Jersey	Oranges			100				100
New Jersey	Oranges					25		25
New Jersey	Oranges					25		25
New Jersey	Oranges					25		25
New Jersey	Oranges					25		25
New Jersey	Oranges	-10						-10
New Jersey	Oranges					25		25
								215
Chicago	Grapes	-1000						-1000
Chicago	Grapes			750				750
Chicago	Grapes					100		100

im trying to enter it into powerbi with a matrix to tell me net inventory. that means if im negative in one material, it needs to be 0 because the materials are not interchangeable. (if i have -200 grapes, it doesnt take away from my oranges)

i have a measure called Sumloc that says 

if(sumx('table','table'[Total])<=0,0, sumx('table','table'[total]))

and a second measure that says

if(HASONEfilter('table'[Material]),[SumLoc],sumx('table',[total]))

the summary looks correct when the matrix is all the way expanded, but when i shrink it down to the location, the numbers are WAY too much.

what am i doing wrong?

[sevenhills]

I am not sure how the matrix looks like...

One thing I noticed is you are using HASONEFILTER, Try using "HASONEVALUE" and see if that fixes

[jdebett]

i have tried both, i'm goign to try and sub out my dataset so i can post the pbix file with "Dummy data" since the actual data is confidential

[v-janeyg-msft]

Hi, @jdebett 

If you're using measure in matrix, It's normal to have problems, because the context of the matrix is complex, it will change after folding, and the value of measure itself will change with the context. You need to formulate a specific measure for the matrix visual.

So can you share some sample data (not a matrix) and your desired result(in visual)? Then we can help you soon.

Best Regards,
Community Support Team _ Janey