cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

How to multiply two columns from two different tables(Direct query)

Hi everyone

I have a report in direct query.

Consider I have 2 tables(table1 and table2) which both have a column that contains stock items. The relation is one to many.

how can I calculate Table1[Qty]*Table2[Price] for each Stock item? (I did it by creating a measure but when I use Card, the filters doesn't apply on it , but in a table, it works perfect)

1 ACCEPTED SOLUTION
Super User

@Matt_Gol , As suggested by @Luuky  Try SUMX with related. If there is 1-M relationship between Table 2 to table 1 it should work

example

Sumx(Table1, Table1[Qty]*related(Table2[Price]))

if not then you might have do more work

example

Sumx(Table1, Table1[Qty]*Maxx(filter(Table2, Table[Item] = Table2[Item]) , Table2[Price]))

you can more conditions in second one as per need

3 REPLIES 3
Helper I

Thank you guys, it works. Much appreciated for your help

Super User

@Matt_Gol , As suggested by @Luuky  Try SUMX with related. If there is 1-M relationship between Table 2 to table 1 it should work

example

Sumx(Table1, Table1[Qty]*related(Table2[Price]))

if not then you might have do more work

example

Sumx(Table1, Table1[Qty]*Maxx(filter(Table2, Table[Item] = Table2[Item]) , Table2[Price]))

you can more conditions in second one as per need

Helper I

Hi @Matt_Gol ,

Try Sumx for it in a measure. If thats not working then I need some visualisation of the tables and how they are related.

Greetings,

Luuk

Helpful resources

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors