How to multiply the value of a column of a table to another column of other table?

[DavidMoreno504]

Hello

Imagine I have two tables:

Table 1:

ID   CountryID     Money

1     MEX                10

2     USA                 20

3     USA                 30

Table2:

ID   CountryID      Currency

1     MEX               2

2     USA               3

So the idea, or what I need, is to travel all along the table1, and for each one row, check the countryID search in Table2 for that countryID, pick the value of currency, and multiply to the money column of Table1

In a programming language, I think that could be:

for(rowTable1 in table1)

{

    for(rowTable2 in table2)

        {

              if(rowTable1.countryID == rowTable2.countryID)

              { 

                   val = rowTable1.money * rowTable2.currency

                   rowTable1.money = val

                   break

               }

        }

}

But I'm not sure how to implement this in an "easy way" in Power BI, when I say in an easy way, it's that maybe it's not neccesary to do a lot of stuff and exists an easy option I didn't think about.

Thank you and regards

[az38 · Accepted solution]

Hi @DavidMoreno504 

as I see your Data Model - the easiest way is to create relationships 'Table1'[CountryID] -> 'Table'[CountryID] (Many to one)

then create a calculated column in Table1:

Column = [money]*related('Table2'[Currency])

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[Tahreem24]

Hi @DavidMoreno504 ,

First, Create relationship between ID column of both the table. Then Create one new mesure like below:

Measure = Sum(Money)*Sum(Currency)

Then you'll get your expected result.

Step 1:  Create relationship between both the tables.

Step 2: Create calculated meausre like below:

Measure = SUm(Table1[Money])*Sum(Table2[Currency])

Please give KUDOS to this effort and accept this as a solution.

[Tahreem24]

@DavidMoreno504 ,

Let me know if my recent post helped you or not.

[DavidMoreno504]

Of course your anwser a really helpful, but it doesn't give what I want as I tell in the post before

I reallly appreciate your help, thanks

[DavidMoreno504]

That's not working as I would like

The problem of that is that first sum all the values so you get, 10 + 20 + 30 = 60

Then, multiplies 60 * 2 = 120 and 60 * 3 = 180

Finally does 120 + 180 = 300.

But what I want is, 

For Mexico, 10 * 2 = 20

For USA, we have 2 values:

20 * 3 = 60

30 * 3 = 90

So the total would be: 20 + 60 + 90 = 170

Thank you for your time

[az38]

Hi @DavidMoreno504 

really?

just set do not summarize in Money-field properties

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[DavidMoreno504]

Ok, now it works, I used your formula instead of the other one, and works fine.

Thank you both!

[DavidMoreno504]

Hi @az38 

Don't know why is not catching it. Attach some pics

So this is the relationship

The details of that relationship, related by CountryID and many to one, both directions

The measure created and it's formula (the same that you posted)

And don't summarize (no resumir)

The result I get

Something it's wrong but I don't know what.

Thank you and regards