How do I get my measure to work correctly? Trying to sum based on different columns.

[jjoers]

This is the data table I am working with. I am trying to find a formula that will combine the two highlighted above. Then I would say the quantity remaining under the filters would have to be greater than 0 so if there was a positive and a negative it would cancel out making it zero. However, when I run my formulat it is not combining these two values and making it 0 it is just picking up the postive number only. Where did I go wrong with my measure and how can I fix this. Thanks!

The table above is pulling data from the table Item_Ledger_Entries_Excel.

The column I am trying to sum is Quantity.

I am attempting to use Lot # and Item number as checks that say if these two numbers match combine them into one value.

I am also filtering this in a date range. The goal of this is to figure out what my inventory level is at the end of that month.

Quantity Remaining =

CALCULATE

(

    SUM('Item_Ledger_Entries_Excel'[Quantity]),

        FILTER('Item_Ledger_Entries_Excel','Item_Ledger_Entries_Excel'[Posting_Date]>=STARTOFMONTH('Table'[Date]) &&

                'Item_Ledger_Entries_Excel'[Posting_Date]<=EOMONTH(MAX('Table'[Date]),0) &&

                'Item_Ledger_Entries_Excel'[Lot_No]='Item_Ledger_Entries_Excel'[Lot_No] &&

                'Item_Ledger_Entries_Excel'[Item_No]='Item_Ledger_Entries_Excel'[Item_No])

)

[jjoers]

No it still didn't work. It has quantities that should cancel out with each other. This just added more to the list. 

[tamerj1]

@jjoers 

Ok, your first example was a little bit misleading. Please try

Quantity Remaining =
CALCULATE (
SUM ( 'Item_Ledger_Entries_Excel'[Quantity] ),
ALL ( 'Item_Ledger_Entries_Excel'[Description], 'Item_Ledger_Entries_Excel'[No] )
)

[jjoers]

It is still doing it. 😞 

[tamerj1]

@jjoers 

One is 4,000 and the other is blank. The total aggression is 4,000

what are we supposed to get in this case?

[jjoers]

The rightmost column in the picture above is the measure "Quantity Remaining". In my data set one of them is a negative number and the other is a postitive so quantity remaining should go to 0. Instead it is showing both of the fields as the full 4,000.

[tamerj1]

@jjoers 

But the quantity ordered shows one positive number and one blank. Believe me if they were one positive number and one negative number they will be aggregated to zero. Please double check. 

Also please check other examples. 

[jjoers]

The bigger issue is the descriptions dont match. That is why I belive they are not coming together correctly. When we recieve the paper it uses a different field then when we job cost it out of the system. That is why there is a descrepency in the description. Would this cause it to not work correctly?

[tamerj1]

@jjoers 

So you want the two lines to be merged into one?

Either to remove description from the filter context or to create a new common description column. No other ways. 

[jjoers]

Yes i would like the two lines to become one, or the quantity remaining to go to 0 to show that it is no longer part of our inventory. So i can do a filter that if Quantity remaining is <=0 to not show it.

[tamerj1]

@jjoers 

Again if the two lines aggregate to zero then they should using my measure but apparently in some occasions they really don't. 
If you want to filter out all of the lines that are repeated then we can do some something else but I need some clear directions from your side. 

[jjoers]

I get that you say they should aggregate to zero but they are not. How do we get them to?

This is the exact data from the table itself you can see one is negative and one is positive. Why are the both coming out positive using the measure?

[tamerj1]

@jjoers 

Then why the quantity measure is showing otherwise?

 

[tamerj1]

Hi @jjoers 

please try

Quantity Remaining =
CALCULATE (
SUM ( 'Item_Ledger_Entries_Excel'[Quantity] ),
ALL ( 'Item_Ledger_Entries_Excel'[Description] )
)