cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wagrezy
Helper I
Helper I

Measure using columns from 2 different tables

Hi,

 

I have the following 2 tables - I would like to create a measure which multiply the units from Table1 with the relevant prices from Table2 (considering item and year). 

I would like to do this using a measure/calculation but without merging/appending queries. I am looking for the most efficient solution given the amount of data could, in reality, be massive.

Could you please help? 

Many thanks.

 

Table1

ItemUnitsYear
A50002020
B100002020
C20002020

 

Table2

ItemPriceYear
A52020
B62020
C72020
A32019
B22019
C42019

 

Yoann

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

Both are new column in table1

//new column table 1 
price tab1 = maxx(filter(table2,table1[Item]=table2[Item] && table1[year]=table2[year]),table2[Price])

value = [price tab1]*[Units]


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

7 REPLIES 7
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try this measure without creating any relationships:

Measure = CALCULATE(SUMX(Table1,Table1[Units]*CALCULATE(SUM('Table2'[Price]),FILTER('Table2','Table2'[Item] in FILTERS('Table1'[Item])))))

Choose [Item] from table1 and this measure as a table visual, it shows:

1.PNG 

 

Best Regards,

Giotto Zhi

Hi,

Many thanks for your reply.

That also applies the calculation to 2019 as there is no relationship on table for years. Again the total rows seems to be much higher than the sum of the 3 rows.

amitchandak
Super User IV
Super User IV

Both are new column in table1

//new column table 1 
price tab1 = maxx(filter(table2,table1[Item]=table2[Item] && table1[year]=table2[year]),table2[Price])

value = [price tab1]*[Units]


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Hi,

Many thanks for your reply, that seems to be the best solution so far. Would you say that the most efficient solution to use?

Hi,

 

In my way, you do not need to create any relationship.

If my answer has solved your issue, please mark it as a solution for others to see it.

 

Best Regards,

Giotto Zhi

bfernandez
Resolver II
Resolver II

Hi @wagrezy 

 

You need to first create the relationship on the Items:

item.png

 

Than create this measure:

Measure = SUM('Table1'[Units]) * SUM('Table2'[Price])
 
Let me know if this solves your problem.
 
If so, please mark this post as the solution to better assist others! 😁

Hi,

Many thanks for your reply, that seems to work only if you create a relationship between the table that involve year and item, i.e. you have to dupplicate columns and merge them. 

Also, I noticed if you create a matrix, the total row would return a result much higher than the sum of the 3 rows.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors