In Matrix Visual - Totals to only sum values greater than 0

[Anonymous]

Hi, I'm currently calculating the current inventory on hand. This is done by summing all movement from previous dates and including the current date. Some days that are negative movements and when obtaining the current inventory it can be negative too. Hence, when presenting the current inventory data in a matrix form, I would like the totals to only sum the positive values and not the negative values. Thanks!

Sample Data:

Product	Movement	Date
A	2	1/1/2020
A	4	1/2/2020
A	5	1/3/2020
A	-9	1/4/2020
A	7	1/5/2020
A	3	1/6/2020
A	1	1/7/2020
A	-3	1/8/2020
A	5	1/9/2020
A	8	1/10/2020
A	10	1/11/2020
A	11	1/12/2020
B	2	1/1/2020
B	-7	1/2/2020
B	5	1/3/2020
B	-9	1/4/2020
B	-7	1/5/2020
B	3	1/6/2020
B	1	1/7/2020
B	-3	1/8/2020
B	-5	1/9/2020
B	8	1/10/2020
B	-10	1/11/2020
B	11	1/12/2020
C	2	1/1/2020
C	5	1/2/2020
C	-3	1/3/2020
C	4	1/4/2020
C	-7	1/5/2020
C	8	1/6/2020
C	1	1/7/2020
C	10	1/8/2020
C	11	1/9/2020
C	-8	1/10/2020
C	12	1/11/2020
C	-5	1/12/2020

Formulas:

1)

Current Inventory =

SWITCH(

TRUE(),

ROUND([Raw Current Inventory],0)=0,BLANK(),

[Raw Current Inventory]<0,BLANK(),

[Raw Current Inventory]

)

2)

Raw Current Inventory =

var _currdate=MAX('Table'[Date])

return

CALCULATE(

SUM('Table'[Movement]),

FILTER(

ALLSELECTED('Table'[Date]),

ISONORAFTER('Table'[Date], _currdate, DESC)

)

)

Power BI Visuals:

[amitchandak]

@Anonymous , prefer a date table so that you can correct inventory by product. Else you have also add product =max product 

Try measure

CALCULATE(
SUMX(filter('Table','Table'[Movement] >0)'Table'[Movement]),
FILTER(
ALLSELECTED('DATE'[Date]),'DATE'[Date] <=max('DATE'[Date])
)
)

or

CALCULATE(
SUM('Table'[Movement]),
FILTER(
ALLSELECTED('DATE'[Date]),'DATE'[Date] <=max('DATE'[Date])
)
)

[Anonymous]

@amitchandak Thanks for your quick response! I just added a date table. Tried your first measure and it works, but it's not what I want. That measure directly excludes all negative movement, but I want to include negative movement as well. I just don't want a negative value in the max date's current inventory total. So, according to the above example, on the maximum date, I have Product A= 44, Product B = -11, Product C = 30. I want the total in matrix form to show 44+30, not 44-11+30, i.e. ignore the negative values in the total.

The second measure you suggested is the same as what I'm doing now for "Raw Current Inventory".

[Anonymous]

Hi @Anonymous ,

You can create two measures as below:

Measure = 
VAR _smovement=CALCULATE(SUM('Table'[Movement]),FILTER('Table','Table'[Product]=MAX('Table'[Product])))
RETURN  
IF(_smovement<0,BLANK(),_smovement)

Current Inventory = SUMX(VALUES('Table'[Product]),[Measure])

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

Hi @Anonymous ,

thanks for your response! your solution does give the desired total. However, I just found out that when I add another field "Plant", I don't get values in matrix form for days where there aren't any movement. This doesn't happen when I don't add plant though (I get the desired data presentation and total when I only include product in the matrix and not plant). I would like to get daily volume, irregardless of whether there was movement or not.

Here's what I get now: (the one on the left is using my measure, while the one on the right uses your measure)

Without plant included: (I get the desired presentation and total with your measure)

Here's the updated data with plant:

ProductMovementDatePlant

A	2	01-Jan-20	F
B	2	01-Jan-20	F
C	2	01-Jan-20	F
A	4	02-Jan-20	F
B	-7	02-Jan-20	G
C	5	02-Jan-20	G
A	5	03-Jan-20	G
B	5	03-Jan-20	F
C	-3	03-Jan-20	F
A	-9	04-Jan-20	F
B	-9	04-Jan-20	F
C		04-Jan-20	F
A	7	05-Jan-20	F
B	-7	05-Jan-20	G
C	-7	05-Jan-20	G
A		06-Jan-20	G
B	3	06-Jan-20	G
C	-8	06-Jan-20	G
A	1	07-Jan-20	G
B	1	07-Jan-20	F
C	1	07-Jan-20	F
A	-3	08-Jan-20	F
B	-3	08-Jan-20	F
C	10	08-Jan-20	F
A	5	09-Jan-20	F
B	-5	09-Jan-20	F
C	11	09-Jan-20	F
A	8	10-Jan-20	F
B	8	10-Jan-20	F
C	-8	10-Jan-20	F
A	10	11-Jan-20	G
B	-10	11-Jan-20	G
C	12	11-Jan-20	G
A	11	12-Jan-20	G
B	11	12-Jan-20	G
C	-5	12-Jan-20	G

[Anonymous · Accepted solution]

Hi @Anonymous ，

I have updated my sample pbix file, please check whether that is what you want. You only need to create a measure as below:

Current Inventory = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[Date],
        'Table'[Plant],
        'Table'[Product],
        "sMovement", IF ( SUM ( 'Table'[Movement] ) < 0, BLANK (), SUM ( 'Table'[Movement] ) )
    )
RETURN
    SUMX ( _tab, [sMovement] )

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.