Combining values from two tables into one

[ritu24raj]

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

 )

[ritu24raj]

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 · Accepted solution]

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.

[Anonymous]

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

[FrankAT]

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]

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