multiplication between two field of two releted table

[Massimo]

hello everyone, I am a new user of powerbi . I want to know how i can , having two related tables one containing , for each product for each sale made , the amount sold and the invoice value ; in the other table I have the unit costs for each product . Should I create a measure that is the result of the multiplication between the quantity sold of each product to the unit cost of each individual product . Thanks for your help.

[CheenuSing · Accepted solution]

@Massimo

Yes you should create a measure for the cost of items sold.

What needs to be done is

1. The Sales and Cost tables should be linked using the Product column in both the tables.

2. Create Measure

    CostofSold:=SUMX('SalesTable',[InvoiceQty] *Related(CostTable[Cost]))

3. This will create total of the cost for all the poroducts sold.

4. This measure will get filtered based on the filters / slicers in your application.

If this is what you are looking for please accept as a solution and also give kudos.

[v-sihou-msft · Accepted solution]

@Massimo

In this scenario, you can create either a measure or a column to get expected result.

Cost_of_Sold_Measure = 
SUMX ( Sales, Sales[Amount Sold] * RELATED ( Cost[Unit Cost] ) )

Cost_of_Sold_Column = 
Sales[Amount Sold] * RELATED ( Cost[Unit Cost] )

Regards,

[Massimo]

Thank may come in handy in other situations  😉

[Baskar]

Hi Massimo,

Cool ya , it is very simple in power BI.

Before creating the new measure ensure u have relationship with two tables. 

we have several options to solve your prob , pls try this below one .

Measure = sum("Table 1 Sales") * sum("Table 2 Cost")

if not , let me know

[perpor]

Thanks !!!!

[CheenuSing · Accepted solution]

@Massimo

Yes you should create a measure for the cost of items sold.

What needs to be done is

1. The Sales and Cost tables should be linked using the Product column in both the tables.

2. Create Measure

    CostofSold:=SUMX('SalesTable',[InvoiceQty] *Related(CostTable[Cost]))

3. This will create total of the cost for all the poroducts sold.

4. This measure will get filtered based on the filters / slicers in your application.

If this is what you are looking for please accept as a solution and also give kudos.

[shoaib986]

I am applying the same formula but SumX is killing with 14 million. Response time SUCKS!

[Daveed1973]

Just what I needed too, thanks.

[Massimo]

Perfect thank you very much! 

[Massimo]

Perfect thank you very much!