Excel Power Query - how to make my tables work?

Hi stefano86 

Add a table with the Unique products. You can make it preferably in Power Query, but for the simplicity and to make it easier to test here it is in DAX:

Unique_SKU_Table = 
DISTINCT(
    UNION(
        SELECTCOLUMNS(
            SeaCare_FULL_Inventory,
            "Unique SKU", [Unique SKU],
            "Vessel", [VESSEL],
            "Product Name", [NAME],
            "Category", [CATEGORY]
        ),
        SELECTCOLUMNS(
            CT_FULL_Inventory,
            "Unique SKU", [Unique SKU],
            "Vessel", [Vessel],
            "Product Name", [Product Name],
            "Category", [CATEGORY]
        )
    )
)

Then create metrics on this new table, to calculate the quantity on each related table.

Total_SeaCare_Quantity =
CALCULATE(
SUM(SeaCare_FULL_Inventory[QUANTITY]),
RELATEDTABLE(Unique_SKU_Table)
)

Total_CrunchTime_Quantity =
CALCULATE(
SUM(CT_FULL_Inventory[Total Quantity]),
RELATEDTABLE(Unique_SKU_Table)
)

Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn