Forum Discussion

newbie74's avatar
newbie74
Frequent Visitor
6 years ago

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:

 

 

 

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

       

    • newbie74's avatar
      newbie74
      Frequent Visitor
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.