Get the Multipication of two Column

[rakeshsaini]

Hi Team ,

 I have a TABLE1 , TABLE1 have COLUMN A , COLUMN  B . And I also have Another Table TABLE2 , it have COLUMN C , COLUMN D. Column A have Realtionship with Column B (Many to one) .

I Want to Get the  Multipication of Values Of COLUMN B AND COLUMN D . How can i get it ?

[Phil_Seamark · Accepted solution]

How about this

New Column = Table1[Total Potential Coils Involved] * RELATED('Table2'[PoundsPerCoil])

And if you want to sum up that column create a measure over it 

[rakeshsaini]

I am Thniking that , is it Possible to Create a Another Table With one Column , that Column Have Multipication Data of those Columns.

[MarcelBeug]

Well, it's quite logical that column A has a relation with column B as they are in the same table: probably the value in column B belongs to the value in column A in the same row...

But what would be the relationship between Table1 and Table2???

Anyhow, as you can see in this video, I created some data in Excel with an additional column COL A to link Table 2 to Table 1 and used Power Query to load them in the datamodel, merge them together and multiply columns B and D.

Similarly, you can merge 2 tables in the Query Editor of Power BI.

[rakeshsaini]

Thanks @MarcelBeug ,

TABLE1 and TABLE2 has Many to one Realtionship .

Yes I Expain this Problem.

TABLE1 Data Columns , Plz see below

and TBALE2 has below Columns

-TBALE1 COLUMN "Part Number Size" has 'Many to one' Realtionship with TABLE2 COLUMN "ProductID" .

Now , I Need to Calculate the Multipication of TABLE1 COLUMN 'Total Potential Coil ' with TABLE2 COLUMN 'Pound Per Coil' respectively .  it Means we will do Multipication Using Many to one Realtionship.!!!

[rakeshsaini]

I used this Formula for Now !!!

Measure--

result=SUMX(TABLE1,Related(TABLE2[PoundsPerCoil]) * TABLE1[Total Potential Coils Involved])

is it correct?

[v-huizhn-msft · Accepted solution]

Hi @rakeshsaini,

The syntax of your formula: result=SUMX(TABLE1,Related(TABLE2[PoundsPerCoil]) * TABLE1[Total Potential Coils Involved])  is right. While you should create a calculated column in Table1 based on my understanding.

You can create a calculated column in Table1 using the formula.

Related(TABLE2[PoundsPerCoil]) * TABLE1[Total Potential Coils Involved]

What's your opinion?

Best Regards,
Aneglia

[Phil_Seamark · Accepted solution]

How about this

New Column = Table1[Total Potential Coils Involved] * RELATED('Table2'[PoundsPerCoil])

And if you want to sum up that column create a measure over it 

[rakeshsaini]

Thanks @Phil_Seamark , Yes Now its giving right Calculations.

[rakeshsaini]

Thanks @Phil_Seamark ,

Actually I am also Confused that it will work or not ? i mean it will give right Answer or not?

[Phil_Seamark]

It should work and should be easy enough to test on some cut down tables.  

[GilbertQ]

Hi @rakeshsaini,

Could you not possible combine all the data into one table in the Query Editor?

Then you would have all the columns in one table, and then the multiplication would be easier?

[rakeshsaini]

Hi @GilbertQ ,i did not got it , can you clairfy ..! How can i Merge Two Table in Single one .

[GilbertQ]

Hi @rakeshsaini,

In the Query Editor you can use the Merge Queries.

When the Merge Queries Window opens, select the columns that you created as part of your relationship.

As well as doing it in the Query Editor is easier, as well as being more efficient in your Power BI Model.

But as suggested below there is always more than one way to achieve the same result.