How to write measure in DAX to sum value for two particular warehouse codes in the same column

Question

Need help in adding the values for two warehouse codes in same column.

Input data:

Warehouse  Stock
GH         6
NSWA       10
NSWA-SW    20
NSWA-01    5
QLDA       15
QLDA-SW    30
GSWG       40
WASA       10
WASA-SW    4
WASA-07    11

My input data has two columns. Warehouse and Stock. EX: NSWA is a main warehouse and NSWA-SW is sub warehouse and all other warehouse codes are small branches. My goal is to sum the stock for all the warehouse codes ending with -SW (sub warehouse NSWA-SW ) and its respective main warehouse (NSWA). My measure should ignore all other warehouse codes.

Expected output:

Warehouse  Stock
GH         6 
NSWA       10
NSWA-SW    30 (sum of NSWA and NSWA-SW)
NSWA-01    5
QLDA       15
QLDA-SW    45 (sum of QLDA and QLDA-SW)
GSWG       40
WASA       10
WASA-SW    14 (sum of WASA and WASA-SW)
WASA-07    11

I could only get the value for the warehouse codes ending with -SW using calculate and filter warehouse code by Right 3 characters.

My Dax Query:

SW Warehouse = Calculate(sum(stock),
                        filter(warehouse, right([warehouse code],3) = "-SW"))

I need the logic how to match the -sw warehouse codes with the respective main branch.

Answer 1

I created a variable MainWarehouse to the main warehouse code by stripping off the "-SW" from any sub-warehouse codes. If the warehouse code doesn't end in "-SW", it's assumed to be a main warehouse, and the code is used as-is.

Adjusted Stock = 
VAR MainWarehouse = 
    IF(
        RIGHT([Warehouse], 3) = "-SW", 
        LEFT([Warehouse], LEN([Warehouse]) - 3), 
        [Warehouse]
    )
RETURN
    CALCULATE(
        SUM([Stock]),
        FILTER(
            ALL('MyTable'),
            [Warehouse] = MainWarehouse ||
            [Warehouse] = MainWarehouse & "-SW"
        )
    )