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
newbie74
Frequent Visitor

Calculate with values from different tables

Hello Community,

(big thanks already for the endless answers i got by browsing this forum!)

 

Here is my problem - i want to create a measure that would output the total rebate in the example below:

 

example problem PowerBi.jpg

 

 

here is the DAX i tried:

TOTAL REBATE = sumX(
ADDCOLUMNS(
SUMMARIZE(
'Table TURNOVER';
'Table TURNOVER'[Eur Order];
'Table REBATE'[rebate]);
"result";
'Table TURNOVER'[Eur Order]*'Table REBATE'[rebate]);
[result])

 

unfortunately it doesn't work.

Many thanks! (sorry if this is really basic...)

 

6 REPLIES 6
Greg_Deckler
Super User
Super User

Perhaps try something like this:

 

 

Measure =
  VAR __Table =
    ADDCOLUMNS(
      ADDCOLUMNS(
        SUMMARIZE(
          'TURNOVER',
          [customer],
          "__EUROrder",SUM('TURNOVER'[EUR Order])
        )
        "__Rebate",LOOKUPVALUE('REBATE'[rebate],'REBIATE'[customer],[customer])
      ),
      "__Total",[__EUROrder] * [__Rebate]
    )
RETURN
  SUMX(__Table,[__Total])

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Many thanks for your help - i realized that by wanting to simplify my case, i misled the helpers like you 😞 

Could you please have a look at the new diagram i just published to explain the exact case.

I could not execute the lookupvalue as you suggested due to this "3 tables " chain.

 

cheers!

Anonymous
Not applicable

Hi @newbie74 

Why don't you merge table orders and table turnover on column orders and then create the measure which i have suggested in previous comment

 

incase if you don't want to merge.

 

measure=Sumx('orders',related(Turnover[orders])*relatedtable(Customer[Rebate]))

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

 

 

@Anonymous  thanks but not there yet .

1) i cannot merge both tables as data goes way beyond my example.

2) there seem to be an error with your formula as in the RELATED formula, the turnover table is not a choice, only the tables that "feeds" into the order table are a possibility (so the rebate table values are accepted).

 

cheers and again thanks for the support!

Anonymous
Not applicable

play with related and relatedtable function


Related works for many to 1

relatedtable works for 1 to many.

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Anonymous
Not applicable

Hi @newbie74 

I assueme both table are connected on Customer.

 

Drag customer, EUR order from first table and rebate from second table into table visual.

 

New Measure=Sumx(table1,table1[Eur ORder]*related(table2[Rebate]))

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

 

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.