Filter CALCULATE SUM by 2 string columns

[alex9999]

Hi,

I am trying to create an inventory management system in Power BI to track materials moving through a unit. I need to be able to create measures for every single item that is available to be moved.

I will need to calculate the sum of the "quantity" that is being moved "IN", once I do that, I can do the same for the material moving "OUT", leaving me with a final figure for the current number of an item which is in the unit.

I need to FILTER by the "Item" and then filter by "Material IN", which are both strings.

Here is what I've tried so far:

Calculate IN = CALCULATE(SUMX('Inventory', 'Inventory'[Quantity]),FILTER (
        ALL ( 'Inventory' ),
        SEARCH ( "Standard 1.00m Spigot", 'Inventory'[Item],) > 0
            && SEARCH ( "In", 'Inventory'[material_InOut_Checkbox],) > 0
    )
)

IN Standard 1.00m Spigot = CALCULATE(SUM('Inventory'[Quantity]), FILTER('Inventory', 'Inventory'[Item] = "Standard 1.00m Spigot"), FILTER('Inventory', 'Inventory'[material_InOut_Checkbox] = "In"))

Here is a sample data set for you to use:

Assuming each "Item" starts with 1000.

Material IN/OUT	Item	Quantity
In	Standard 1.00m Spigot	3
Out	Standard 1.00m Spigot	10
In	Standard 1.00m Spigot	5

There will be 998 Standard 1.00m Spigot's in the unit. 

There will be over 50 items to record so I will need to filter by each one to get an exact figure for the item's count in the unit.

Thanks in advance 🙂

[Daryl-Lynch-Bzy · Accepted solution]

Hi @alex9999 , would you consider the following for your Inventory Management solution:

DateTime	Action	Item Key	Quantity
01/01/2022 00:00	Stocktake	1	10
15/01/2022 12:23	In	1	3
16/01/2022 17:23	Out	1	-10
20/01/2022 11:23	In	1	5
01/02/2022 00:00	Stocktake	1	7
15/02/2022 12:23	In	1	3
16/02/2022 17:23	Out	1	-10
20/02/2022 11:23	In	1	5

The features of this approach is that In and Out quantity movements are assigned + and - sign accordingly, so it make summing to find the current Inventory quantity much easier.  The second feature is the addition of the Stocktake action.  This allows you to reset your inventory without reporting as In and Out.  This could make it easier to track missing quantity or incorrect In/Out records.

Note you should create a Item table.  This table will have the Item Key (primary) and Item details.  You can also add a calculated column with the current quantity using the following DAX expression (note this is the quantity at point of time the model is refreshed):

Current Item Quantity = //Calculated Column for Item table
VAR _LastStocktake = SUMMARIZE( FILTER(Inventory, Inventory[Action] = "Stocktake") , "Last Stocktake", MAX(Inventory[DateTime]))
VAR _StockMovementsSinceLastStockTake = CALCULATE( SUM(Inventory[Quantity]) , Inventory[DateTime] >= _LastStocktake )
RETURN 
_StockMovementsSinceLastStockTake

From a measure perspective, the following allows you to show stock levels over time:

Daily Stock Level =
//Measure
VAR _Test =
    ISFILTERED ( 'Item'[Item Key] )
VAR _Date =
    MIN (
        MIN ( 'Calendar'[Date] ),
        TODAY ()
    )
VAR _LastStocktake =
    SUMMARIZE (
        FILTER (
            Inventory,
            Inventory[DateTime] <= _Date
                && Inventory[Action] = "Stocktake"
        ),
        "LastStockTake", MAX ( Inventory[DateTime] )
    )
RETURN
    IF (
        _Test,
        CALCULATE (
            SUM ( Inventory[Quantity] ),
            Inventory[DateTime] >= _LastStocktake,
            Inventory[DateTime] <= _Date
        ),
        "No Result"
    )

Note the Isfilter stops the measure from calculating when the item is not filtered