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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
JohannesM
Helper I
Helper I

Getting the column sum when multiplying with another table.

Hi,

 

I have two tables and I'm using DAX to multiply a column from table1 with a column from table2. The two tables are connected by a key so it is multiplying per row (the key is built on Group and Year).
It all looks good if I include both columns in the visualisation but if I remove the column from table2 then the sum I get is as if I muliplied with the total from table2, not row by row. My data is sensitive but it looks like this:

 

table1                                             table2

GroupYearCost  GroupYearProd
A20201  A202016
A20213  B202015
A20225  C202018
B20203  A20213
B20216  B20211
B20228  C202118
C20205  A202218
C20216  B202219
C20227  C20223

 

Measure1 =

VAR cost = SUM('table1[Cost])

VAR prod = SUM('table2[Prod])

RETURN

cost*prod

 

I guess I need to tell DAX to sum Prod row by row and I have tried but with no success. Suggestions anyone?

 

Thanks in advance!

3 REPLIES 3
FreemanZ
Super User
Super User

hi @JohannesM 

it needs both columns to find the unique match.

Actually you can use Merge Query in Power Query, by bring the Table1[Cost] column to Table2. Ensure to select both the group and year in merging. Check this:

https://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616...

 

 

Actually the problem I have is that not both columns can have an unique match since it's one-to-many. But I'm sure that the multiplying is possible. It is in the column with unique match that I want to get the row instead of column when multiplying.

Hi @FreemanZ 
But isn't that what I already do with the relation in PBIs "Model"-page?  RowID in the picture below is based on Group and Year to get that unique match. 

JohannesM_0-1673535712097.png

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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