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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Michel89
Regular Visitor

Calculation between two tables

Hello everybody,

 

I'm new to PowerBI and have a question about creating a query / formula that can calculate a value from cells of different tables. 

I have a table called 'Werkzaamheden' and a table called 'Kosten' These are shown below:

 

Werkzaamheden

 

Kosten

Kosten

 

Now. I want to create a query / formula that calculates, for each row in the Werkzaamheden table, what the multiplied costs are.

This should be done as follows:

Werkzaamheden[Duur] * (Kosten[Prijs] from the row at which Kosten[Functie] is equal to Werkzaamheden[Functie])

 

Can anybody help me with this? I can't seem to figure it out...

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I think you are trying to create a MEASURE (meetwaarde), you should create a COLUMN (kolom). Add the column to the [Werkzaamheden] table and not the [Kosten] table. I just tried and it worked 🙂

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

As jthomso mentioned, If you create a link between the two Functie columns, you can add a calculated column to the Werkzaamheden table:

 

KostenWerkzaamheden = Werkzaamheden[Duur] * RELATED(Kosten[Kosten])

That one should work 🙂 

I've tried adding a column to the table 'Werkzaamheden', but got an error:

Expression.Error: The name RELATED hasn't been recognized.

 

= Table.AddColumn(#"Type gewijzigd", "KostenWerkzaamheden", each Werkzaamheden[Duur] * RELATED(Kosten[Kosten]))

 

I don't know what I'm doing wrong? 

Anonymous
Not applicable

Are you using PowerBI Desktop? Looks like your adding the column in the wrong place (the query editor instead of in the table). Check out this video about how to add a calculated column to a table:

https://www.youtube.com/watch?v=62mLfiNcqVM

 

Yes, I am using the PowerBI Desktop

 

I've tried this first, but then I got a notification that no single value for Werkzaamheden[Duur] could be selected.

Foutmelding.JPG

 

(Sorry, language is set to Dutch in PowerBI)

Anonymous
Not applicable

I think you are trying to create a MEASURE (meetwaarde), you should create a COLUMN (kolom). Add the column to the [Werkzaamheden] table and not the [Kosten] table. I just tried and it worked 🙂

Thanks so much! It works!

jthomson
Solution Sage
Solution Sage

Shouldn't be much wrong with your formula as it is assuming your two tables are related on the Functie column, you may need to use RELATED to pull through your Prijs value

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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