How to calculate measures with data on different tables

[Morrison]

Hello,

I would need your help to understand how to solve this situation:

I have a table with the product data

- Product key

- Description

- Etc..

A stock table

- Product key

- Stock

- Unit Purchase Cost

- Stock Valuation (Stocks * Unit purchase cost)

A table with the sales of the shops

- Product key

- Date

- Quantities Sold

- Net sales amount

I then calculated measures for:

- Average daily quantity sold (Quantity Sold / # days of sale)

- Stock of goods in the warehouse to deal with "N" rotation days (Average daily quantity sold * No. of rotation days)

Now I would like to calculate the amount of surplus goods in stock and the value of the surplus goods

- Quantity of excess goods = Headquarters Inventory (Warehouse Table) - Goods inventory in warehouse to cope with "N" rotation days (Measure)

- Valuation of Excess Goods = Quantity of Excess Goods (Measure) * Unit Purchase Cost (Warehouse Table or Sold Table)

To calculate these two measures, having the information on different tables I cannot find the solution

Thank you

[v-tangjie-msft · Accepted solution]

Hi @Morrison ,

Based on your description, you can try to take advantage of the Filter() and Sumx() functions to calculate measures with data on different tables.

It may be helpful to refer to the following post:

Solved: Measure with data from different tables - Microsoft Power BI Community

Solved: Measure with fields from two different tables - Microsoft Power BI Community

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.