Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Matrix Total incorrect when collapsed

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?

3 REPLIES 3
Super User

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

Regular Visitor

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

Community Support

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors