Inventory On Hand Measure Works per Product, but Total is Overstated

[Hizqeel]

Hi everyone,

I'm working on an Inventory Valuation measure in Power BI, but the numbers aren't adding up correctly. I have two fact tables — one is a Transaction table that holds Units Sold for each Product ID, and another is an Inventory table that includes Purchases for each Product ID. My goal is to calculate how much inventory is on hand by subtracting total units sold from total purchases, and then multiplying that result by the product's cost price from the Product table.

The problem is that my Inventory Valuation measure is giving me a result of $1.4B, but based on the data, it should be closer to $586M. I'm using a DAX measure that sums purchases and sales per product using SUMX over VALUES(Product[Product ID]), and multiplies that by the cost price. But I think the way Power BI is aggregating values from two separate fact tables might be causing this issue — maybe it's duplicating cost price or not respecting granularity properly.

I’ve made sure my relationships are correct. Transaction is many-to-one with Product, and Product is one-to-many with Inventory. I'm also using a Date table to slice by time.

I just want to understand why the number is so inflated and how to correctly handle calculations like these that involve two different fact tables and a lookup to cost price. Any help, feedback, or advice would be appreciated.

Thanks a lot!

In the table view (attached), the Inventory On Hand, Cost Price per product, and Inventory Valuation per product are correct. However, the total sum of the Inventory Valuation is not matching the expected result. I’ve manually checked in Excel, and the correct total should be $586M, but the KPI card is showing approximately $1.4B (rounded). I've also attached the Data Model view, and as mentioned, the KPI view is showing an incorrect total for Inventory Valuation — it should be $586M.

Dax Using for Inventory Valuation:

Inventory Valuation(KPI) =

SUMX(

    VALUES('Product'[Product ID]),

    VAR Purchases = CALCULATE(SUM('Inventory'[Purchases]))

    VAR Sales = CALCULATE(SUM('Transaction'[Units Sold]))

    VAR InventoryQty = Purchases - Sales

    VAR Cost = MAX('Product'[Cost Price])

    RETURN

        InventoryQty * Cost

)

 

[v-hjannapu]

Hi @Hizqeel,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[FBergamaschi]

I integrate the answer you alreay got from another support person: you have a data model full of bidirectional relationships. I suggest you to change that into simpler moodirectional and revising the data model. With multiple fact tables, bidirectional relationships can lead to unexpected results and/or ambiguity

[DataNinja777 · Accepted solution]

Hi @Hizqeel ,

The problem you're facing with your Inventory Valuation measure is that the grand total is inflated because your DateTable only filters your sales data, not your purchase data. According to your data model, a relationship exists between the DateTable and your Transaction table, but not with your Inventory table.

Consequently, when you apply a date filter, your measure calculates InventoryQty by subtracting sales from the selected time period from purchases over all time. This mismatch leads to an artificially high inventory quantity and, therefore, an incorrect total valuation of $1.4B instead of the expected $586M.

The most effective solution is to correct the data model by establishing a relationship between your DateTable and your Inventory table. In the Power BI Model view, you can drag the Date column from the DateTable to the PurchaseDate column in the Inventory table. This will create the necessary active relationship, ensuring that any date filters apply equally to both your purchases and sales data. Once this is done, your original DAX measure should work correctly without any changes.

If you're unable to modify the data model, you can use a more robust DAX measure instead. This alternative calculates the inventory value as of the last date in the current filter context (e.g., the end of the selected year or month). This approach manually applies the time intelligence to both sides of the inventory calculation.

Inventory Valuation (Corrected) =
SUMX(
    'Product',
    VAR MaxDate = MAX('DateTable'[Date]) 
    VAR PurchasesToDate =
        CALCULATE(
            SUM('Inventory'[Purchases]),
            'Inventory'[PurchaseDate] <= MaxDate,
            ALL('DateTable') 
        )
    VAR SalesToDate =
        CALCULATE(
            SUM('Transaction'[Units Sold]),
            'Transaction'[OrderDate] <= MaxDate,
            ALL('DateTable')
        )
    VAR InventoryQty = COALESCE(PurchasesToDate, 0) - COALESCE(SalesToDate, 0)
    VAR Cost = 'Product'[Cost Price]
    RETURN
        InventoryQty * Cost
)

This DAX formula works by first identifying the latest date in the current filter context (MaxDate). It then calculates the cumulative total of purchases and sales up to that date, ensuring a true point-in-time inventory quantity. The COALESCE function is used to handle any products that might have sales but no purchases (or vice-versa) by treating BLANK values as zero, thus preventing errors in the final calculation.

Best regards,

[Hizqeel]

Thank you so much for the guidance and identification of the error. It's working now.