topic Calculate with values from different tables in DAX Commands and Tips

Calculate with values from different tables

newbie74 — Mon, 09 Mar 2020 12:47:31 GMT

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:

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...)

Re: Calculate with values from different tables

Greg_Deckler — Mon, 09 Mar 2020 00:15:04 GMT

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])

Re: Calculate with values from different tables

Anonymous — Mon, 09 Mar 2020 12:04:04 GMT

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.

Re: Calculate with values from different tables

newbie74 — Mon, 09 Mar 2020 12:49:24 GMT

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!

Re: Calculate with values from different tables

Anonymous — Mon, 09 Mar 2020 12:55:43 GMT

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.

Re: Calculate with values from different tables

newbie74 — Mon, 09 Mar 2020 13:36:59 GMT

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!

Re: Calculate with values from different tables

Anonymous — Tue, 10 Mar 2020 11:38:46 GMT

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.