cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors