Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Item | Units | Year |
A | 5000 | 2020 |
B | 10000 | 2020 |
C | 2000 | 2020 |
Table2
Item | Price | Year |
A | 5 | 2020 |
B | 6 | 2020 |
C | 7 | 2020 |
A | 3 | 2019 |
B | 2 | 2019 |
C | 4 | 2019 |
Yoann
Solved! Go to Solution.
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]
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:
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.
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]
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
Hi @wagrezy
You need to first create the relationship on the Items:
Than create this measure:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |