Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

How to multiply two columns from diferent tables

Hi. I'm trying to multiply two values: a) Yield (Rendimiento) y b) Price per kg (Precio por Kg) to get the total revenue per hectare. However, i have this 2 columns in 2 differents tables, wich are indirectly related and when i try to multiply those two values i get an ERROR result. 

 

I've 4 tables with 4 kind of data (Total production, Yield, Harvest area and Price per kg), and in each table i've 3 main categories (Year = Años, State = Departamentos and Crop = Cultivo); and 3 other tables (one for each category) which are related to the other 4 tables.

 

I'd like to get the total revenue per hectare (by multiplying the yield and price per hectare) by Year, State and Crop but i can't figure out how to do it with DAX formulas.

 

Hope you could help me and thanks.

 

Andrés

 

Data SIEA.png

 

 

 

 

1 ACCEPTED SOLUTION

Hi @Anonymous

 

It should hopefully work, it is sometimes just a different way of thinking.

 

And to explain a bit more is because you are appending 3 datasets, for dataset 1 you might have all the values for your column called DataSet1Values, and when you append dataset 2 and dataset 3 for the same column (DataSet1Values) all the values will be NULL. So when you load it into the data model, and then create a measure on your column DataSet1Values, it will sum all the values. Which should then still give you the same result.

 

I hope that the above makes sense.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

7 REPLIES 7
GilbertQ
Super User
Super User

Hi @Anonymous

 

What I would do if I was working with your data, was to go into the Query Editor and Combine the 4 tables together with the Append Queries.

 

The reason for this is when you Append because you have got the 3 values (Year = Años, State = Departamentos and Crop = Cultivo) with the same name the values will then all append into the same column.

 

What will happen is that you will then get the additional columns with the different names in the Append Query. Which in turn has the columns in the same table. 

 

So from there you can now create a measure for your calculation?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Hi @GilbertQ.

 

When i append the 4 tables i do get additional columns, but they're quite strange. Instead of just adding 3 new columns with the new data, mainteing their order and the same number of rows, it's seems that tables are just pasted one under the other with 4 columns but with data in only one at the time (see the image), so i can't make any calculation. If i could get them right, with the data appearing in the 4 columns at the same time, then I could work.

 

Hope you can help me, and thank you beforehand.

 

 

Consulta powerbi 1.png

Hi @Anonymous

 

Once you have appended the data and loaded it into the Data Model, have you then tried to create your calculation?

 

The reason I ask this, is even though some of the columns might have NULL values, when you sum the entire column, it should then get you the correct totals?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Actually i haven't, but i'll try it right away. Hope it works

Hi @Anonymous

 

It should hopefully work, it is sometimes just a different way of thinking.

 

And to explain a bit more is because you are appending 3 datasets, for dataset 1 you might have all the values for your column called DataSet1Values, and when you append dataset 2 and dataset 3 for the same column (DataSet1Values) all the values will be NULL. So when you load it into the data model, and then create a measure on your column DataSet1Values, it will sum all the values. Which should then still give you the same result.

 

I hope that the above makes sense.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Hi @GilbertQ.

 

You were right, even though i got a lot of "nulls" in my appended table, using the DAXs formulas i could calculate the rentability per hectare for each state, year and crop =D That thing was driving me nuts. I didn't know the DAXs formulas could be used that way, neither that they could function with a lot of null values.

 

Thank you very much¡¡ =D

 

 

Hi @Anonymous

 

Glad that it worked and solved your issue.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors