Forum Discussion

ritu24raj's avatar
ritu24raj
Icon for Helper II rankHelper II
5 years ago
Solved

Combining values from two tables into one

Hi -

 

I need some help, in regards to merge the values from two different tables based on one value.

Table 1

Table 2

As we can see the Revenue In and Revenue Out values are different in both the tables. Now, when I creating a resultant table both the values are appearing to be the same.

 

Resultant Table

The values of the Delivery region and PO contract region are the same. I tried creating a DAX to get the revenue out values in the table.

 

DAX -

Revenue Out = 

CALCULATE(SUMX(Registration,[Order USD Amount Total]),

 FILTER(Class,[Cross Region Transfer1]="Yes" || [Cross Region Transfer2]="Yes"),

 ALLSELECTED(Registration[Purchase Order Contract Region])

 )
Need Help
Tips and Tricks
  • Icey's avatar
    Icey
    5 years ago

    Hi ritu24raj , 

     

    Since you are using SSAS >> Live connection, it is suggested to create a new table "Region", which contains regions both from "PO Contract Region" and "Delivery Region",  and create relationships among other tables in SSAS.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi ritu24raj 

    you didn't describe the relationships of your data model, so here is what might be step further:

     

     

    Sum of Revenue Out = SUM('Table Rev Out'[Revenue Out])

Sum of Revenue In = SUM('Table Rev In'[Revenue In])

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

    • ritu24raj's avatar
      ritu24raj
      Icon for Helper II rankHelper II
      5 years ago

      Hi FrankAT -

       

      Thanks for this, I am using SSAS >> Live connection to cube to connect to Power BI and I can't create manual relationships in the backend.

       

      The only thing I can do is w/ the help of DAX

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Can you just use summarize to create the new resultant table with the RevIN data then add a column with a value filter?

     

     

    ResultTable = SUMMARIZE(testdata2,TestData2[Delivery Reg],TestData2[RevIN])
    NewRevOutColumn = CALCULATE(values(TestData1[Revout]),FILTER(TestData1,ResultTable[Delivery Reg]=TestData1[PO]))
     

  • ritu24raj's avatar
    ritu24raj
    Icon for Helper II rankHelper II
    5 years ago

    HI Anonymous - This is not working as expected.

    1. Revenue In and Revenue Out are two different measures
    2. Delivery Region and PO Region are coming from two separate objects

     

    • Icey's avatar
      Icey
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi ritu24raj , 

       

      Since you are using SSAS >> Live connection, it is suggested to create a new table "Region", which contains regions both from "PO Contract Region" and "Delivery Region",  and create relationships among other tables in SSAS.

       

       

      Best Regards,

      Icey

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.