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

 

 

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.

     

     

    • newbie74's avatar
      newbie74
      Frequent Visitor
      6 years ago

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

        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.