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

[BhargavVM]

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

Please find the input data

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.

Please find the output

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.

I am using direct query for connection to semantic model cannot use power query.

[Walter_W2022]

Hi @BhargavVM , 

Please see below measure, it should work, the pbix file attached as well. please let me know whether it works or not 

_total =

VAR _sw_wh = FILTER(warehouse,RIGHT([warehouse_code],3) = "-SW")

VAR _temp_table =

    ADDCOLUMNS(

        ADDCOLUMNS(

            _sw_wh,

            "p_wh", LEFT([warehouse_code],4),

            "p_stock", LOOKUPVALUE(warehouse[stock],

                                    warehouse[warehouse_code],LEFT([warehouse_code],4)

                                )

        ),

        "Total", [stock]+[p_stock]

    )

VAR _result = CALCULATE(SUMX(_temp_table,[Total]))

RETURN

_result

20240311-file-1.pbix

[BhargavVM]

Hi @Walter_W2022 
In my table I have duplicates. It is giving me error in this line

            "p_stock", LOOKUPVALUE(warehouse[stock],

                                    warehouse[warehouse_code],LEFT([warehouse_code],4)

For one warehouse there are multiple rows. How can we aggregate this stock and lookup the value.

[Walter_W2022]

I am sorry @BhargavVM , I could not figure out if there are duplicates and also cannot create virtual table. there are a lot of limitations

[BhargavVM]

Hi No worries Walter. Thanks for providing the solution. I will use as a starting point and try to figure out. Thanks for your help.

[Walter_W2022]

No problem @BhargavVM , it is my pleasure.

[Walter_W2022]

@BhargavVM , Please refer to below attached file, I created a virtual table to lookup the stock of parent warehouses, then sum them up to get total, please let me know if you have any questions.

Result:

Virtual table created:

Model:

20240311-file.pbix

[BhargavVM]

Hi @Walter_W2022 

Thanks for your response.
In my case, I cannot add a new table to my data. I am using direct query mode connecting to the PowerBi semantic model. (My report does not allow me to create either calculated table or calculated column).

It is tricky to work with measure in this case. If you have any alternate solutions using measure, your help will be much appreciated.

Thanks