Tables Relationship and calculations

[MarkEi]

Dear all,

I'm very new to the Power BI platform and need to create some application.

My target is as follows :

Let's say I have a few fruit stocks.  

Also, I have very long list Product_Name vs Qty. for each stock.

These lists can include repetitive items.

I want to create a table that will show me the calculated quantities in every stock.

There is an example of how it should looks like in the image below (Final Calculated Stock) :

Also, I would want to know whether it can be done without using queries.

Any help would be appreciated 🙂

[v-yulgu-msft · Accepted solution]

Hi @MarkEi,

Establish one to many relationships between product table and stock tables based on [Product_Name] field.

Then, add 'Product List'[Product_Name], 'FirstStock'[Qty] and 'SecondStock'[Qty] to table visual. Choose "Sum" to aggregate Qty column.

Alternatively, you can new a calculated table, then, insert a Matrix to display data.

Table1 = UNION(ADDCOLUMNS(FirstStock,"Category","FirstStock"),ADDCOLUMNS(SecondStock,"Category","SecondStock"))

Best regards,

Yuliana Gu

[nirvana_moksh]

If you have multiple Product Name lists then when importing all these lists create a UNION of all these and you will have a single query and then when you create a data table and doing all transformations -> Add Product Name and Qty and then you can 'SUM' the quantity easily using the drop down pill next to QTY under the Values tab on the right.